0

Working on a function to put the filename in a specific column (P) of a file. I've got this running if I specify the cells to put the filename in (e.g. P1:P5).

However, I want to get this to run in the P column, but for all rows that have data in the A column.

I know I could do it for just the whole P column, but i dont want it to run on empty rows (they're of no use)

Code I have so far:

Sub Save_files()    
    Cells.Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Rows("1:3").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("P2").Select
    ActiveCell.FormulaR1C1 = "=CELL(""filename"")"
    Range("P2").Select
    Selection.Copy
    Range("p1:p5").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("D4").Select
End Sub

I want to replace Range("p1:p5").Select with something that selects every P cell that is on a row with data in A of the same row.

Things to note:

  • Column A will always have data
  • Columns B through to O may or may not have data

Thanks in advance!

braX
  • 11,506
  • 5
  • 20
  • 33
Tom
  • 51
  • 7
  • 1
    See [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for how to find the last row based on column A. – BigBen Jan 16 '20 at 13:45
  • 1
    After that, perhaps give [this question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) a read too – BigBen Jan 16 '20 at 13:46

1 Answers1

0

I changed:

    Range("p1:p5").Select

to:

    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Range("p1:p" & LastRow).Select
Tom
  • 51
  • 7