Locating the row that has no values. Selecting that row till the last row after that and deleting it

  • Thread starter Thread starter VB Script for Excel
  • Start date Start date
V

VB Script for Excel

Everyday, I get a file that has several rows and columns with data. I
wanted to build a macro that can locate first row after the data that
has no values in any cells. Then, i want to select thar row (lets say
row 105) and select it till the last row i.e. 65536 or so. And then
delete those. (yes, although it doens't have any values, i still need
to delete those once)

Please help. I think there should be some loop function that can locate
the row that has no values. Then select it up to 65000th row and delete
those.

It's really getting hard to figure it out.

Thanks
 
try the below

LastRow = Range("A1").End(xlDown).Row

LastRow is type Long, change the start point from A2 to your data start point
 
Sub Delete_Past_LastCell()
Dim RealLastRow As Long
Dim RealLastColumn As Long
On Error Resume Next
RealLastRow = _
Cells.Find("*", Range("A1"), , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", Range("A1"), , , xlByColumns, xlPrevious).Column
set rng = Cells(RealLastRow, RealLastColumn).Offset(1,0)
range(rng,cells(rows.count,1)).EntireRow.Delete
End Sub
 
Back
Top