Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting visible rows
Hello.
I've searched and tried most of the day today, but so far I haven't found a solution. I have an autofiltered list where I need to perform operations on the visible data only. It's some quite extensive lookup functions, and can take a while to run. So I want to have my macro run on the visible data only, not the entire 1000x70 table. So far I have been able to determine the rownumber of the last piece of data by using Last = Cells(Rows.Count, 1).End(xlUp).Row I can't figure out why Last = UsedRange.Rows.SpecialCells(xlVisible, xlTextValues).Count won't work, though. How can I determine which row numbers to run my macro on? The data is sorted, but not consecutive. Maybe there is a way to determine if a row is visible before running the code on it Maybe there is a way to determine the first and last visible row, and run the code on all rows in between, should be faster than running it on all, but not very elegant. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting visible rows
With ActiveSheet.UsedRange
visRows = Application.Intersect(.Columns(1), ..SpecialCells(xlCellTypeVisible)).Count End With But I don't think that's what you want, maybe - For r = firstRow To lastRow If Not Rows(r).Hidden Then For c = leftCol To rightCol 'process Cells(r, c) Next End If Next Regards, Peter T "atledreier" wrote in message ps.com... Hello. I've searched and tried most of the day today, but so far I haven't found a solution. I have an autofiltered list where I need to perform operations on the visible data only. It's some quite extensive lookup functions, and can take a while to run. So I want to have my macro run on the visible data only, not the entire 1000x70 table. So far I have been able to determine the rownumber of the last piece of data by using Last = Cells(Rows.Count, 1).End(xlUp).Row I can't figure out why Last = UsedRange.Rows.SpecialCells(xlVisible, xlTextValues).Count won't work, though. How can I determine which row numbers to run my macro on? The data is sorted, but not consecutive. Maybe there is a way to determine if a row is visible before running the code on it Maybe there is a way to determine the first and last visible row, and run the code on all rows in between, should be faster than running it on all, but not very elegant. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rows for visible cells | Excel Programming | |||
Counting visible cells | Excel Programming | |||
Counting rows, then counting values. | Excel Discussion (Misc queries) | |||
Counting Rows Then Counting Values in Columns | Excel Programming | |||
Counting visible cells | Excel Programming |