1

How do I adjust the below code to work with the equivalent of crtl shift end in VBA (which I think is xlLastCell or xlCellTypeLastCell)?

My sheet has 64 columns I only want to select Column 1 to 58 where I have data starting on row 2. The below works unless the sheet is blank and then all rows from row 2 to 1048575 are selected. Because I have protected cells below row 200,000 my script errors with the below.

With Worksheet(WorkSheet)
.Range(.Cells(2,1).End(XlDown),.Cells(2,58)).Select
End With
Selection.ClearContents
Community
  • 1
  • 1
user1609391
  • 445
  • 1
  • 9
  • 24

1 Answers1

0

Start at row 200,000 and look up.

With Worksheet(WorkSheet)
    .Range(.Cells(200000, "A").End(XlUP), .Cells(2, "BF")).ClearContents
End With

'possible alternate to avoid clearing row 1
With Worksheet(WorkSheet)
    .Range(.Cells(200000, "A").End(XlUP).offset(1, 0), .Cells(2, "BF")).ClearContents
End With
  • thank you. Any way to do this dynamically? I have other functions that determine the first row with data and which columns that will be selected. I put value above in my example but these are determine dynamically. I don't want to hard code the 200000 in case this changes in the future. – user1609391 Oct 30 '17 at 17:59
  • 1
    That sounds like another question. –  Oct 30 '17 at 18:00