VBA MACRO Emergency!!!!

  • Thread starter Thread starter OriginalStealth
  • Start date Start date
O

OriginalStealth

My spreadsheet has hundreds of rows with blank rows in between. The only way
I know that I am at the end of the data is to find 20 consecutive blank rows.
Example below. I need to evaluate row, if this is not the last row then
proceed with my other logic so forth and so on. If it is the last row, I
need to return to A7 and proceed with my other logic.


A
1.XXXXXXXX
2.
3.XXXXXXXX
4.XXXXXXXX
5.XXXXXXXX
6.
7.
8.
9.XXXXXXXX
10.XXXXXXXX
11-20 are blank

Thanks in advance OS
 
The easiest way to find your end of data is :
LastRow = [A65000].End(xlUp).Row
Regards.
Daniel
 
OriginalStealth said:
My spreadsheet has hundreds of rows with blank rows in between.  The only way
I know that I am at the end of the data is to find 20 consecutive blank rows.
Click to expand...
....

Easiest to find the last nonblank row searching from the bottom row
up.

In VBA you can determine the last nonblank row number in a given
column using

n = RangeReference.EntireColumn.Cells(Rows.Count, 1).End(xlUp).Row
If IsEmpty(RangeReference.EntireColumn.Cells(n, 1).Value2) Then n = 0
MsgBox "Last nonblank row: " & CStr(n)

The result, n, would be 0 when there are no nonblank cells in the
column containing the leftmost column of RangeReference.
 
Some alternatives:

1) Remove blank rows:

http://www.ozgrid.com/VBA/VBACode.htm

2) Change your code to run until it reaches the last used cell in column A:

Function AddrLastColA()
Range("A65536").End(xlup).Select
AddrLastColA = Selection.Address
End Function

3) Have your code start at the last used cell in column A and work upwards.
 
Hi,

Of course we don't know what you are trying to accomplish but don't forget
you can apply AutoFilter to hide all non-blank rows, which might lead to a
completely different but efficient approach to your problem.

However, if I read you message correctly you are doing something as you work
your way down, your not just trying to find the last cell in the column? If
there are 21 blank rows have you gone too far? Or are you saying that if
there are 20 blank rows then all the rest are blank?
 
Back
Top