Explain Macro?

  • Thread starter Thread starter Stu
  • Start date Start date
S

Stu

Could someone please explain these two macros in plain English for me:

1st macro:

Public Sub All()

Dim hideRange As Range
Dim unhideRange As Range
Dim Rng As Range
For Each Rng In Range("B3:B21,B28:B45,B52:B69," & _
"B76:B93,B100:B117,B124:B141,B148:B165," & _
"B172:B189,B196:B213,B220:B237,B244:B261,B268:B285")
If Rng.Value = "" Then
If hideRange Is Nothing Then
Set hideRange = Rng.Offset(1, 0)
Else
Set hideRange = Union(hideRange, Rng.Offset(1, 0))
End If
Else
If unhideRange Is Nothing Then
Set unhideRange = Rng.Offset(1, 0)
Else
Set unhideRange = Union(unhideRange, Rng.Offset(1, 0))
End If
End If

Next Rng
If Not hideRange Is Nothing Then _
hideRange.EntireRow.Hidden = True
If Not unhideRange Is Nothing Then _
unhideRange.EntireRow.Hidden = False
End Sub




2nd Macro:


Sub MainHide()
Application.ScreenUpdating = False
Main.UnProtect Password:="Jan"
Dim hideRows As Range
With Range("G3:G2401")
.EntireRow.Hidden = False
.AutoFilter Field:=1, Criteria1:="="
On Error Resume Next
Set hideRows = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
Selection.AutoFilter
If Not hideRows Is Nothing Then hideRows.EntireRow.Hidden = True
Range("G202,G402,G602,G802,G1002,G1202,G1402," & _
"G1602,G1802,G2002,G2202,G2402").EntireRow.Hidden = False
Main.Protect Password:="Jan"
Application.ScreenUpdating = True
End Sub
 
The first goes through the list of cells, and if the cell is blank, records
a reference to the row below it for hiding (hideRange). If it isn't blank
it puts the reference to the row below in the unhide list. Then, if there
are any references in each list, it hides or unhides.

The second uses an autofilter to filter so only the blank rows in column G
are visible. It uses special cells to select those cells, then it removes
the autofilter, then it offsets one row from each identified cell and hides
those rows. This approach would not work in Excel 97 since, for a
discontiguous range, it offsets only the first area.
 
Hi Stu.

I really don't like the way the first one is written.
Its bugging me so I had to do this.


Sub MyPPp()
Dim x As Long, y As Long, Str As String
Application.Cells.Rows.Hidden = False
Str = MsgBox("to Hide blanks pick YES, to Hide content pick NO", vbYesNoCancel)
If Str = vbCancel Then End
For x = 4 To 285 Step 24
For y = x To x + 17
If Str = vbYes Then
If Cells(y, 2) = "" Then Cells(y, 3) = "=1/0"
Else
If Cells(y, 2) <> "" Then Cells(y, 3) = "=1/0"
End If
Next y
Next x
Range("C4:C285").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Hidden = True
Columns(3).ClearContents
End Sub


If you already have data in your C column, can you just insert a new column and hide it?
This one I hope is a little more clear to understand.
I know it supposed to start in B3, but the above wouldn't work without a lot more code just to deal with one cell. You could just
as easily do whatever manually to B3.

As for the second macro, I'm not going there.


Regards Robert
 
Can you tell me why that macro was bugging you Robert?
And what's wrong with the second one?

And I actually got these macros from this newsgroup back in 2003, and they
have worked fine for me, no errors that I know of.

--
Thanks,
Stu



Robert McCurdy said:
Hi Stu.

I really don't like the way the first one is written.
Its bugging me so I had to do this.


Sub MyPPp()
Dim x As Long, y As Long, Str As String
Application.Cells.Rows.Hidden = False
Str = MsgBox("to Hide blanks pick YES, to Hide content pick NO", vbYesNoCancel)
If Str = vbCancel Then End
For x = 4 To 285 Step 24
For y = x To x + 17
If Str = vbYes Then
If Cells(y, 2) = "" Then Cells(y, 3) = "=1/0"
Else
If Cells(y, 2) <> "" Then Cells(y, 3) = "=1/0"
End If
Next y
Next x
Range("C4:C285").SpecialCells(xlCellTypeFormulas,
Click to expand...
xlErrors).EntireRow.Hidden = True
Columns(3).ClearContents
End Sub


If you already have data in your C column, can you just insert a new column and hide it?
This one I hope is a little more clear to understand.
I know it supposed to start in B3, but the above wouldn't work without a
Click to expand...
lot more code just to deal with one cell. You could just
 
What "bugs" you about the code, Robert?

Your alternative unhides rows outside the range of interest (presumably
the reason for having an unhiderange at all); requires significantly
more range resolution than a For Each...Next; adds a couple hundred
calculation cycles; and adds unasked-for user intervention. In addition,
it gives a run-time error if there are no blank (or non-blank) cells in
the range of interest.

Disclaimer: TomO and I (independently) came up with the subject code, so
I'm hardly unbiased.

BTW, it only takes one minor modification to your code to account for B3:


For y = x + (x = 4) to x + 17
 
The macro cycles through the 12 ranges of 17 (or 18, for the first)
cells.

If a cell is blank, it assigns the cell below it to the hiderange Range.
Union() gives a run-time error if one of the ranges is Nothing, so
hiderange is first checked - if it's Nothing, then the cell below is
assigned, if it's not Nothing (i.e., cell(s) have been assigned to it),
then the cell below is added using Union().

If the cell is not blank, then the cell below it is assigned to the
unhiderange.

After all cells are assigned to either one range or the other, the hide
range (if it's had any cells assigned) is hidden and the unhiderange (if
it's had any cells assigned) is unhidden.
 
Back
Top