Variable unknown rows (Excel 97 SR1 NT4)

Topic

New Reply

Greetings all.

In the process of trying to code a long transaction macro I have a general (and hopefully) simple question to the gallery before I begin.

I have 4 product groups that I will be calculating discounts, one after the other.
Before I do that I have to prep the sheet and find the last row for the whole data and then begin with each product group, pulled from data filter. So my first section, the prep is fine:
Dim lRow As Long
lRow = Cells(Application.Rows.Count, Columns(“B”).Column).End(xlUp).Row

But each group will be identified from the filter row in Column G – and the length will be unknown. If I apply the same coding – ie
Dim lRowA ‘(product A – Last Row)
Selection.AutoFilter Field:=7, Criteria1:=”=A-*”, Operator:=xlAnd
lRowA = Cells(Application.Rows.Count, Columns(“G”).Column).End(xlUp).Row
then onto lRowA, lRowB, lRowC, etc
As the filter will only show visible applicable cells, the original lRow would not be applicable.

So –
Will my value for the original lRow change? As I am still doing calculations before and after these sorts, I would need to keep that lRow value constant.

Or is there an easier way to get last row for the sheet and each of the four variable last rows without changing their respective numbers?

Reply | Quote

Replies

If the selection data is in column G and is fltered, can you use a variation on:

for each rngCell in Columns(“G:G”).SpecialCells(xlCellTypeVisible)
rngCell.Offset(row, column).

Reply | Quote

Vunderbar!
Simple, yet effect.
That did the trick.

Thanks.

Reply | Quote

Ooops. Spoke to soon. It seemed to work wonderfully for one stage. In another stage, using similar coding, I asked it to write a formula to one cell, then to another cell.

The problem is, is that Excel is writing the formulas down the entire column! I thought for each row select:
For Each rs In Columns(“G:G”).SpecialCells(xlCellTypeVisible)
that only the rows affected by the filter would be calculated upon. This is not the case. Once the formula is copied down to the bottom of the filtered data shown, it keeps on going.

How do I say visible cells only with value?
I used: if range(rc,”G”)”” then …
but that, and any variation to identify just cells with a value come up in error.
Am I just missing a simple syntax or something altogether?!

Reply | Quote

Apologies, I’m the one who should say “oops”.Obviously that method I proposed would select all the blank cells. The SpecialCells method can be “stacked” like this:

For Each rngCell in Columns(“G:G”).SpecialCells(xlCellTypeConstants, 1).SpecialCells(xlCellTypeVisible)

Adjust the first SpecialCells(argument) according to the content of your column G, formulas, text, numbers, etc. Review all the possible SpecialCell arguments in XL VBA Help, and you can also use the Edit, GoTo, Special dialog as a reminder of all the possibilities. (Recording macros using GoTo, Special is a quick way to learn how to use SpecialCells.)

It’s possible that SpecialCells may not fit the bill in which case you’d have to go back to your original approach.

Reply | Quote

After banging my head against a brick wall trying to figure out why this thing wasn’t working, one simple change in syntax (and approach did the trick):

For Each PRow In Columns(“H:H”).SpecialCells(xlCellTypeVisible)
If PRow “” Then ….
… End If
Next PRow
and that ladies and gentlemen solved the dilemma. I am STILL slapping myself for not clueing into that simple arrangment sooner.

I thank you for your assistance in pointing me the right way.

Reply | Quote