This will prove useful to exactly no one but me, and yet, I’m going to record it anyway because I really, really want to record it. Y’know? I want the Internet to know I did this with little or no help. I figured it out.
Sometimes when I write VBA macros, I have to filter the sheets and make sure there’s something there afterward. That is, occasionally, when you filter for a value and there aren’t any matching values for Excel to show, there is only a header row left.
Other times, there are rows left, but they come way down on the sheet. So, you have the header row and then something like row 462, then row 487, then rows 489-546, then row…get it?
This is a non-contiguous range in Excel, and it’s problematic when you want to count how many rows are actually showing.
There’s a way to do this using the
.SpecialCells(xlCellTypeVisible) property of the range class in Excel. Don’t understand what that means? Well, a range in Excel is a collection of cells, arranged in rows and columns. With me so far? When you apply filtering to a worksheet in Excel (which you might know as a “tab” now), you ask it to hide those rows which don’t have a matching value in the field upon which you’re filtering. With me at this point too? So you have fields A, B and C, and you have a thousand rows with those columns filled in. You filter the sheet for value “123” in field (or column) A, and that means the first 200 rows are hidden because they don’t have “123” in column (field) A.
So, when I ask Excel how many rows are visible, it says “1”, because it can’t count the rows in the non-contiguous areas of the sheet. So it only sees the top row, then a lot of nothing, so it says, “1. Just 1.’
Still here? Okay, good.
So I have a lot of macros lately which, for whatever reason, require me to count the rows left over after filtering. So far, I’ve not had much issue. But the last two have been especially ornery to get through, and what I just figured out now is, I have a way I can do this easily and reliably.
.Find method of the range object in Excel VBA which allows me to search for something. The method returns a range object, which includes an address reference, a row index, a column index, and a value, among other things. So, to get around the non-contiguous area problem after filtering, I simply do a find for the very first thing of any sort the
.Find method can locate.
This is done by searching for “*” (that’s an asterisk, in case it’s not clear on the screen) and going backward from the first cell, and returning the
.Row property of the range object returned.
In other words, I tell the
.Find method to go find “*”, which means anything, and give me the row number of whatever it is you found. If it’s greater than one…winner winner! Chicken dinner! I know we have more than just the header on the sheet showing! W00t!
The code looks like this:
rowNumber = rng.Find(what:="*", after:=.Range("A1"), lookat:=xlPart, LookIn:=xlValues, _
searchorder:=xlByRows, searchdirection:=xlPrevious, MatchCase:=False).Row
…where of course rowNumber is either an integer or long integer to house the row number (which can be over 1,000,000 in Excel versions 2007 and later).
Now, I’ve slowly been coming up to speed on Visual Basic for Applications development, which is, in my opinion, quite different than Excel macros, though the language used is the same. For development, I’ll need to know a LOT more about things like classes, how to construct custom objects, and when and how to use classes. They’ll make the development of software much more flexible, scalable, and easier to maintain, though the upfront time cost might be higher initially.
But I don’t have time to implement that right now. In my head, I have a vague idea of how it might work, and how I might have been able to use it in this case, but I can’t afford the time to go back and revise. So, eh.
Anyway, I have this going all over the place so forgive me if you see it from me like six times. Feel free to delete, but doggone, I was happy about figuring this out.
Shabbat Shalom, y’all, and I’ll see ya on the other side.