Looping Macro's with merged cells

BenC1985 · Jan 27th 2013

Hi all!

I've only just registered today and new to VB macro's and just reading through other peoples posts on here has helped me get as far as I have, but I still need a bit of help.

I have created a spreadsheet that has a table of 100 rows, but I have had to merge the cells for this so it is really 300 rows. I put a macro in to hide each 3 rows when cell in column C was blank, because these are merged cells I don't think I can put a range into the macro, and I am unsure of how to loop the first macro to repeat every third row.

Below is an example of the code, these steps go up to Row 316.

Code

'Macro to be used to hide unused rows'
Sub HideRows()
    
    If Range("c16").Value = 0 Then
        Rows("16:18").EntireRow.Hidden = True
    Else
        Rows("16:18").EntireRow.Hidden = False
    End If
     
    If Range("c19").Value = 0 Then
        Rows("19:21").EntireRow.Hidden = True
    Else
        Rows("19:21").EntireRow.Hidden = False
    End If
    
    If Range("C22").Value = 0 Then
        Rows("22:24").EntireRow.Hidden = True
    Else
        Rows("22:24").EntireRow.Hidden = False
    End If
     
    If Range("C25").Value = 0 Then
        Rows("25:27").EntireRow.Hidden = True
    Else
        Rows("25:27").EntireRow.Hidden = False
    End If
     
    If Range("c28").Value = 0 Then
        Rows("28:30").EntireRow.Hidden = True
    Else
        Rows("28:30").EntireRow.Hidden = False
    End If
    
    If Range("c31").Value = 0 Then
        Rows("31:33").EntireRow.Hidden = True
    Else
        Rows("31:33").EntireRow.Hidden = False
    End If
    
    If Range("c34").Value = 0 Then
        Rows("34:36").EntireRow.Hidden = True
    Else
        Rows("34:36").EntireRow.Hidden = False
    End If
    
    If Range("c37").Value = 0 Then
        Rows("37:39").EntireRow.Hidden = True
    Else
        Rows("37:39").EntireRow.Hidden = False
    End If
     
    If Range("c40").Value = 0 Then
        Rows("40:42").EntireRow.Hidden = True
    Else
        Rows("40:42").EntireRow.Hidden = False
    End If
    
    If Range("C43").Value = 0 Then
        Rows("43:45").EntireRow.Hidden = True
    Else
        Rows("43:45").EntireRow.Hidden = False
    End If
     
    If Range("C46").Value = 0 Then
        Rows("46:48").EntireRow.Hidden = True
    Else
        Rows("46:48").EntireRow.Hidden = False
    End If
     
    If Range("c49").Value = 0 Then
        Rows("49:51").EntireRow.Hidden = True
    Else
        Rows("49:51").EntireRow.Hidden = False
    End If

Display More

I also have the same looping query about a different macro on the same sheet, this macro is to stamp the date and time into a cell, so when cell G16 - Z16 is changed, the cell 2 rows down is stamped with username and date. I can't seem to write this macro more than once for each row.

Code

'Macro to add Date and username into comments'
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Set rng1 = Intersect(Range("G16:Z16"), Target)
If rng1 Is Nothing Then Exit Sub
Application.EnableEvents = False
rng1.Offset(2, 0).Value = Date & " - " & Environ("username")
Application.EnableEvents = True
End Sub

Any help would be greatly appreciated!

**cytop** · Jan 27th 2013

Re: Looping Macro's with merged cells

Perhaps, for the first part...

Code

Sub HideRows() 
    
    Dim lngRow as long 


    '// Change ending value to suit
    For lngRow = 16 to 49 Step 3
        Rows(cStr(lngrow) &":" & cStr(lngRow + 2)).EntireRow.Hidden =  Range("C" & cStr(lngRow)).Value = 0
    Next
 End Sub

Code typed freehand and totally untested...

Not sure what you mean by

Quote

I can't seem to write this macro more than once for each row

BenC1985 · Jan 27th 2013

Re: Looping Macro's with merged cells

Cytop, this works great!

I was hoping that with a shorter macro, it wouldn't take as long but it seems to take around the same amount of time. It's a lot cleaner though, thank you!

**cytop** · Jan 27th 2013

Re: Looping Macro's with merged cells

It's the simplest way - but you could turn screen updating off to speed things up.

Code

Sub HideRows() 
     
    Dim lngRow As Long 
    
    '// With the environment changed, (screen updating), it's ESSENTIAL to have an error handler
    '// in case it throws a wobbly.
    On Error Goto ErrHandler


    Application.ScreenUpdating = False


 
     '// Change ending value to suit
    For lngRow = 16 To 49 Step 3 
        Rows(CStr(lngrow) &":" & CStr(lngRow + 2)).EntireRow.Hidden =  Range("C" & CStr(lngRow)).Value = 0 
    Next 


ErrHandler:
    
    '// Not interested in errors - simply here to re-enable screen updating
    Application.ScreenUpdating = True


End Sub

Display More

BenC1985 · Jan 27th 2013

Re: Looping Macro's with merged cells

So for the second part, I have tried just duplicating the macro so writing:

Code

'Macro to add Date and username into comments'
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Set rng1 = Intersect(Range("G16:Z16", "G19:Z19"), Target)
If rng1 Is Nothing Then Exit Sub
Application.EnableEvents = False
rng1.Offset(2, 0).Value = Date & " - " & Environ("username")
Application.EnableEvents = TrueEnd Sub


Dim rng2 As Range
Set rng2 = Intersect(Range("G19:Z19"), Target)
If rng2 Is Nothing Then Exit Sub
Application.EnableEvents = False
rng2.Offset(2, 0).Value = Date & " - " & Environ("username")
Application.EnableEvents = TrueEnd Sub

Display More

But that didn't work I can do this over 2 rows by using:

Code

Dim rng1 As Range
Set rng1 = Intersect(Range("G16:Z16", "G19:Z19"), Target)
If rng1 Is Nothing Then Exit Sub
Application.EnableEvents = False
rng1.Offset(2, 0).Value = Date & " - " & Environ("username")
Application.EnableEvents = TrueEnd Sub

but I can't get this to work for rows G22:Z22 - G313:Z313

BenC1985 · Jan 27th 2013

Re: Looping Macro's with merged cells

I am planning on activating the first macro from a button on a different sheet, this seems to make it quicker, so speed isn't a massive issue, thank you.

**cytop** · Jan 27th 2013

Re: Looping Macro's with merged cells

Trying to understand this one...

If any cell in the range G16:Z16 is changed, you want to write the Date and User Name to AB16...?

Same if changes are on Row 19, 22 ... 316.

BenC1985 · Jan 27th 2013

Re: Looping Macro's with merged cells

Changes made to cell G16:Z16 I would like the username and date putting in cell G18:Z18 (2 cells down from the changed cell)

Repeated to Rows G313:Z313 - username and date in G315:Z315

**cytop** · Jan 27th 2013

Re: Looping Macro's with merged cells

Silly me, misread the Row/Column offset parameters...

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    
    On Error GoTo ErrHandler
    
    If Target.Row Mod 3 = 1 And _
       (Target.Row >= 16 And Target.Row <= 315) And _
       (Target.Column >= 7 And Target.Column <= 26) Then
       
       Application.EnableEvents = False
       Target.Offset(2, 0).Value = Date & " - " & Environ("username")
       
    End If
    
ErrHandler:
    Application.EnableEvents = True
    
End Sub

Display More

would normally do it... but you mentioned the cells are merged...?

Quote

...but I have had to merge the cells for this

You cannot have different values in merged cells. The top left cell takes priority...

BenC1985 · Jan 27th 2013

Re: Looping Macro's with merged cells

That is great! Works like a dream!

These ones aren't merged, the cells to the left of this are merged.

**cytop** · Jan 27th 2013

Re: Looping Macro's with merged cells

OK...

Looping Macro's with merged cells

Participate now!

Share