Last visible row or column in a worksheet

[stranno]

Sometimes you want to know the last visible row or column in a worksheet. I have tested several methods and i could not find a method which works under all thinkable circumstances. Specially when the autofilter is active, most methods will give wrong answers. In the attached workbook i wrote a more consistant procedure which also works when filters are active and rows and column are hidden.

Please comment on this contribution, test it in any possible way and report any problem.

Thanks in advance

Stranno

PS
The error handling is poor and just provisionally.

[mancubus]

hi stranno

http://excelvbamacro.com/how-to-find...-in-excel.html

look for the posts by Nuovella down to the page.

[stranno]

Hi Mancubus,

Thanks for your comment. I've checked the code you refered to.
But it only returns the last row of the usedrange which is not always
the real last row.

[mancubus]

wellcome.

perhaps, removing any filters first, then applying ExcelDiet (available in the forum) to downsize the UsedRange to range with data, and last using above mentioned procedures may help.

[stranno]

Yes You're right. But I think my procedure is less complicated. Simplicity, that's where it goes about don't you think?

If you remove any filters then you have to store them somewhere in order to
regain the original settings.

regards,
Stranno

[mancubus]

as per your file:

UsedRange : A3:Q589
has filters
has two hidden rows (23 and 30) each row contains 1 nonblank cell. B23 and A30.
downmost row : 15 (an example... after filter...)
upmost row: 3
leftmost column : 1
rightmost column : 6


your function returns 15.
if this is what you want, then ok.
but and i can get this number by commonly used one-liner: "find method."

[VBA]lastRow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
[/VBA]

or another commonly used
[VBA]lastRow = Cells(Rows.Count, "A").End(xlUp).Row
[/VBA]

but if you want to get the real last row, for data entry for ex, you must take into account cell A30 that contains data.
in this case last row is 30.

in order to get this number i would
- remove any filters, make hidden rows and columns visible,
- delete columns G:IV, delete rows 31:65536,
- filter data, hide rows, whatever, where necessary,
- then use below code to obtain the last row.

[VBA]
Dim lastRow As Long
With ActiveSheet.UsedRange
lastRow = .Rows(.Rows.Count).Row
End With
[/VBA]


ps: i also appreciate any other coding from members.

[mikerickson]

How about this
[VBA]Sub trial()
Dim oneCol As Range
Dim reallyUsedRows As Range

With ActiveSheet
Set reallyUsedRows = .UsedRange.Rows(1)
For Each oneCol In .UsedRange.Columns
Set reallyUsedRows = Range(reallyUsedRows, oneCol.EntireColumn.Cells(.Rows.Count, 1).End(xlUp))
Next oneCol
End With

With reallyUsedRows.SpecialCells(xlCellTypeVisible)
With .Areas(.Areas.Count)
MsgBox .Item(.Cells.Count).Row & " is the last visible row"
End With
End With
End Sub[/VBA]

[stranno]

Hi Mancubus,

Yes 15 is what i want. It's the last visible row isn't it?

The one liner: lastrow = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

is not reliable. And that's the whole point. Open my attached wb push FindLastRow look at the result. Then hide the last row by filtering 19 out (column A) then again push Findlastrow and you'll see that the result isn't right.

lastRow = Cells(Rows.Count, "A").End(xlUp).Row is allright. But this method looks at one column (A in this case) at the time, not at the entire sheet.

By the way, how can i insert those nice green codefields?

regards,
Stranno

[stranno]

Hi Mikerickson,

I'll try your proposal. Although i don't understand it at first sight.
What is happening here for instance: "Set reallyUsedRows = Range(reallyUsedRows, oneCol.EntireColumn.Cells(.Rows.Count, 1).End(xlUp))"

Is it still a fast method if, lets say, more than 200 columns are involved?

Have you used this of code more often? I mean will it work under all thinkable circumstances?

[mancubus]

that's ok then stranno.
i assumed you were trying to get the last available row number. but not sure of it. thats why i said if it is 15...



click on the green vba button in the message or advanced panel. or write manually the vba tags.

[stranno]

[VBA]Thanx mancubus.[/VBA]

[mikerickson]

The code that I posted is pretty robust. Is there a particular situation that you are concerned about?

No, I don't use it often because the spreadsheets that I write don't have long dangling columns where I don't expect them. i.e. I know which column is going to be the longest, so I only test that one column. So, I don't have to find the longest column, I know it before writing the code.

What that code is doing is looping through each column and making ReallyUsedRows a large enough to contain all the cells in that column.

Note that I'm using .End(xlup), that will include formulas that evaluate to "", if you want to exclude those cells use Mancubus' .Find approach from post #6.

Is it a fast method? Sure. A loop like this (doesn't change any screen elements, does not create or alter any Objects) isn't noticeable until you get in the 10,000's of columns. I wouldn't put that procedure inside a loop of any length though. i.e. if you have 100 worksheet and want to do this for all those sheets 100X200 = 20,000 which is in the noticeable range.

[stranno]

Hi mikerickson,

Thanks for your detailed respons.
You asked me whether i was concerned about a particular situation.
Well, i recently discovered that the find method for this purpose (finding the last row) is a little tricky. Were you aware of this outcome (see my post #8)?
I didn't know until i screwed up an entire database.