Banding - visible rows only?

  • Thread starter Thread starter NorTor
  • Start date Start date
N

NorTor

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
 
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
 
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
 
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
 
As I mentioned, random rows (dependent on user filtering) are hidden
by the code: Rows(ActiveCell.Row).Hidden = True
Click to expand...

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
 
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.
 
Back
Top