Selecting the next blank cell on 2 columns

[TM74]

Hi,

I have a file that I create daily using VBA. In the middle of the process, some data is added into a new worksheet - columns A&B and columns D&E. On one day, the data in columns A&B could stretch to 100 rows, and the data in columns D&E could only be 2 rows. The next day the used rows could be reversed (D&E could have more used rows than A&B).

The problem that I have is that the file size is huge at the end of the process and the problem appears to be with the blank cells underneath this data. Therefore I would like to select the next blank cell in the longest column and then delete all rows underneath it (doing this manually solves the file size problem).

I can't work out any VBA to select the column with the longest amount of used rows in - can anyone help?

 

[VoG]

Try like this

Sub Test2()
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Rows(LR & ":" & Rows.Count).Clear
End Sub

 

[TM74]

Thanks. That worked well.

 

[TM74]

Hi,

I used this vba in my file today and found that it deleted the last row of data in the longest column. I also tested it in a dummy workbook and found the same problem.

Do you have any idea why? Should there be an offset in the second row of text?

 

[VoG]

Yes, sorry

Sub Test2()
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Rows(LR + 1 & ":" & Rows.Count).Clear
End Sub

 

[TM74]

Many thanks!