Posts by BenC1985

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.

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

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!

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!