Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Banding - visible rows only?
Hello,
I am filtering roughly 500 rows of data, and depending on the user's choice, some rows are hidden. For appearances, I like to do a banding of the data. Normally I would use VBA like the following: Cells(1, 1).Activate j = 2 Do Until IsEmpty(Cells(j, 1)) Range("A" & j & ":" & "W" & j).Interior.ColorIndex = 40 j = j + 2 Loop I hide the rows filtered using this code: Rows(ActiveCell.Row).Hidden = True All replies very much appreciated! Best regards, -NorTor |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Banding - visible rows only?
Look good!
You don't need Cells(1, 1).Activate If you mean to do the whole row, then instead of: Range("A" .... ).Interior... try: Rows(j).Interior.... Here's my version: Sub testit() Dim i As Long, lngLastRow As Long With Sheet1 lngLastRow = .Cells(1, 1).End(xlDown).Row For i = 2 To lngLastRow Step 2 .Rows(i).Interior.ColorIndex = 35 Next End With End Sub Rob "NorTor" wrote in message ... Hello, I am filtering roughly 500 rows of data, and depending on the user's choice, some rows are hidden. For appearances, I like to do a banding of the data. Normally I would use VBA like the following: Cells(1, 1).Activate j = 2 Do Until IsEmpty(Cells(j, 1)) Range("A" & j & ":" & "W" & j).Interior.ColorIndex = 40 j = j + 2 Loop I hide the rows filtered using this code: Rows(ActiveCell.Row).Hidden = True All replies very much appreciated! Best regards, -NorTor |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Banding - visible rows only?
if you have filtering in place and want to band only the visible rows
*AND* you have something in Column A for every row you can use SUBTOTAL(3,... in your Conditional Formula, which is COUNTA a count of the non empty cells in the list. Use =MOD(SUBTOTAL(3,$A$1:$A1),2) for alternate row banding Use =MOD(SUBTOTAL(3,$A$1:$A1),3) for every 3rd row colored My page on Conditional Formatting is http://www.mvps.org/dmcritchie/excel/condfmt.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "NorTor" wrote in message ... Hello, I am filtering roughly 500 rows of data, and depending on the user's choice, some rows are hidden. For appearances, I like to do a banding of the data. Normally I would use VBA like the following: Cells(1, 1).Activate j = 2 Do Until IsEmpty(Cells(j, 1)) Range("A" & j & ":" & "W" & j).Interior.ColorIndex = 40 j = j + 2 Loop I hide the rows filtered using this code: Rows(ActiveCell.Row).Hidden = True All replies very much appreciated! Best regards, -NorTor |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Banding - visible rows only?
Dear Rob,
thank you for your reply. I tried you code, and it does more or less what mine does, except you do the whole row. What I mean, is that when I have hidden some rows, they will still be counted it the loop, so that the banding does not appear correctly (eg you will have to colored rows together if you hide one in between). As I mentioned, random rows (dependent on user filtering) are hidden by the code: Rows(ActiveCell.Row).Hidden = True And I want the banding to count only the rows that are visible after the user has done the filtering, in a way that every second row is colored and the ones in between are un-colored. Help please? Cheers, -NorTor On Mon, 12 Jan 2004 17:33:36 +1300, "Rob van Gelder" wrote: Look good! You don't need Cells(1, 1).Activate If you mean to do the whole row, then instead of: Range("A" ... ).Interior... try: Rows(j).Interior.... Here's my version: Sub testit() Dim i As Long, lngLastRow As Long With Sheet1 lngLastRow = .Cells(1, 1).End(xlDown).Row For i = 2 To lngLastRow Step 2 .Rows(i).Interior.ColorIndex = 35 Next End With End Sub Rob "NorTor" wrote in message .. . Hello, I am filtering roughly 500 rows of data, and depending on the user's choice, some rows are hidden. For appearances, I like to do a banding of the data. Normally I would use VBA like the following: Cells(1, 1).Activate j = 2 Do Until IsEmpty(Cells(j, 1)) Range("A" & j & ":" & "W" & j).Interior.ColorIndex = 40 j = j + 2 Loop I hide the rows filtered using this code: Rows(ActiveCell.Row).Hidden = True All replies very much appreciated! Best regards, -NorTor |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Banding - visible rows only?
As I mentioned, random rows (dependent on user filtering) are hidden
by the code: Rows(ActiveCell.Row).Hidden = True That is not filtering, and you did not mention it quite that way before. What have are hidden rows (basically zero height). You would first have the macro remove the interior color from all rows then since you would not want the top row colored, you can start from the top and work on the non hidden rows. Option Explicit Sub Band_alternate_35() 'David McRitchie, programming, 2004-01-12 'Color Band alternate rows not hidden by user '-- as in .... Rows(ActiveCell.Row).Hidden = True '-- not for use on filtered rows 'almost 5 minutes to do 20000 rows Dim i As Long, nothidden As Boolean i = ActiveSheet.UsedRange.Rows.Count '-- fix usedrange Cells.Interior.ColorIndex = xlNone Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For i = 1 To Cells.SpecialCells(xlLastCell).Row If Not Rows(i).hidden Then nothidden = nothidden + 1 If Not nothidden Then Rows(i).Interior.ColorIndex = 35 End If Next i Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "NorTor" wrote in message ... As I mentioned, random rows (dependent on user filtering) are hidden by the code: Rows(ActiveCell.Row).Hidden = True And I want the banding to count only the rows that are visible after the user has done the filtering, in a way that every second row is colored and the ones in between are un-colored. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Banding - visible rows only?
When I provided a macro to test for hidden (not filtered) rows,
I neglected to say that I do not think a macro can see therefore cannot test on Filtered rows, checking is on hidden and that difference is why others probably did not attempt to answer, or failed in their testing. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use VBA to shade every 3 rows (banding) | Excel Discussion (Misc queries) | |||
Deleting Visible Rows | Excel Discussion (Misc queries) | |||
Visible Rows Indexing | Excel Programming | |||
List Box Visible Rows Only | Excel Programming | |||
AutoFilter - which rows are currently visible? | Excel Programming |