Find Last Used Cell of Visible Cells

[YellowLabPro]

Provided w/ a tip to use SpecialCells(xlCellTypeVisible) to copy filtered cells, can I use SpecialCells to count rows?

This does not work, but hopefully illustrates the idea of the desired result.

lLrwt = Wss2.SpecialCells(xlCellTypeVisible).Cells(Rows.Count, "A").End(xlUp).Row

[Bob Phillips]

lLrwt = Wss2.Columns(1).SpecialCells(xlCellTypeVisible).Cells(Rows.Count, "A").End(xlUp).Row

[Bob Phillips]

ACtually, I don't think you even need SpecialCells

lLrwt = Wss2.Cells(Rows.Count, "A").End(xlUp).Row

[p45cal]

if Wss2 is a single column of the whole unfiltered range then

Range("A2:A21").SpecialCells(xlCellTypeVisible).Count

specifically:

Wss2.SpecialCells(xlCellTypeVisible).Count

[YellowLabPro]

Thanks Bob,
The last used cell on the sheet is in fact 12764, lLrwt reflects this in the Locals window. However, the number of visible rows are 89.
So what is happening here? Is it not counting and just returning the value of the last row?
If this is the case how do we alter it to count?

[YellowLabPro]

Bob-
I had this exact line prior, and it returns the value of 12764, it does not count the visible rows only, it returns the value of the last visible row.

[YellowLabPro]

This sheet formula returns the count of 89,

=SUBTOTAL(103,C2:C12764)

[Norie]

Doug

If you just want to count based on some criteria why not use something like COUNTIF?

[YellowLabPro]

Norie,
Sorry, I don't understand your suggestion as it pertains to this issue.
Maybe COUNTIF will work, but it seems to break rank w/ how the rest of my code is structured.
I can locate the correct amount of rows if I move my variable line after the last line of SpecialCells. Copy, Highlighted in Red for an easy read. This works because now the code has something it can count on the target worksheet.
But this in my opinion is a hack method.
My problem w/ this is that it fixes something that should be handled initially, not on the rebound. Since WsS2, the source worksheet where the visible cells are located, it seems that there should be an easy way to count the visible cells on the Source Sheet and obtain count value at that point.
It is a matter of trying to write this as smoothly as possible, my method here shows a lack of style and agility.... trying to improve upon this.

Set WsS = Workbooks("MasterImportSheetWebStore.xls").Sheets("TGFF")
Set WsS2 = Workbooks("MasterImportSheetWebStore.xls").Sheets("PCCombined_FF")
Set WsT = Workbooks("MaintImport.xls").Sheets("Record Creator")
lLrwS = WsS.Cells(Rows.Count, "A").End(xlUp).Row
'lLrwT = WsS2.Cells(Rows.Count, "A").End(xlUp).Row
lLrwT = WsS2.Columns(1).SpecialCells(xlCellTypeVisible).Cells(Rows.Count, "A").End(xlUp).Row
'lLrwT = WsS2.Cells.SpecialCells(xlCellTypeVisible).Cells(Rows.Count, "A").Row
WsS2.Range("B2").Resize(lLrwS - 1).SpecialCells(xlCellTypeVisible).Copy WsT.Cells(6, "W") 'Record# Cols. B2>>W6
WsS2.Range("E2").Resize(lLrwS - 1).SpecialCells(xlCellTypeVisible).Copy WsT.Cells(6, "AC") 'Qty. Cols. E2>>AC6
WsS2.Range("F2").Resize(lLrwS - 1).SpecialCells(xlCellTypeVisible).Copy WsT.Cells(6, "AD") 'Dept. Cols. F2>>AD6
WsS2.Range("G2").Resize(lLrwS - 1).SpecialCells(xlCellTypeVisible).Copy WsT.Cells(6, "AE") 'Cat. Cols. G2>>AE6
WsS2.Range("H2").Resize(lLrwS - 1).SpecialCells(xlCellTypeVisible).Copy WsT.Cells(6, "AA") 'Price Cols. H2>>AA6
lLrwT = WsT.Cells(Rows.Count, "W").End(xlUp).Row

[Norie]

Doug

You seemed to be asking asked how to count filtered rows.

Obviously the filter would be based on some criteria I assume, so why not use COUNTIF or a similar formula.

If the real issue is copying based on some criteria I would suggest another method. eg advanced filter or even a loop