View Full Version : Find Last Used Cell of Visible Cells
YellowLabPro
08-27-2007, 04:02 AM
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
08-27-2007, 04:09 AM
lLrwt = Wss2.Columns(1).SpecialCells(xlCellTypeVisible).Cells(Rows.Count, "A").End(xlUp).Row
Bob Phillips
08-27-2007, 04:11 AM
ACtually, I don't think you even need SpecialCells
lLrwt = Wss2.Cells(Rows.Count, "A").End(xlUp).Row
p45cal
08-27-2007, 04:14 AM
if Wss2 is a single column of the whole unfiltered range then
Range("A2:A21").SpecialCells(xlCellTypeVisible).Count
specifically:
Wss2.SpecialCells(xlCellTypeVisible).Count
YellowLabPro
08-27-2007, 04:17 AM
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
08-27-2007, 04:20 AM
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
08-27-2007, 04:21 AM
This sheet formula returns the count of 89,
=SUBTOTAL(103,C2:C12764)
Norie
08-27-2007, 05:44 AM
Doug
If you just want to count based on some criteria why not use something like COUNTIF?
YellowLabPro
08-27-2007, 06:04 AM
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
08-27-2007, 08:14 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.