Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Row Hiding Macro (2k3)

    I want to automate a process that basically hides rows of data sets that contain "CRMA" in the data set title as shown in the attached file. I don't need the whole macro written - but I need the syntax to hide rows so I can atleast try to write it. I'm not sure where to start with this one at the moment. Can anyone point me in the right direction?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Row Hiding Macro (2k3)

    The syntax to hide rows 3 through 5 is

    Range("A3:A5").EntireRow.Hidden = True

  3. #3
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Row Hiding Macro (2k3)

    Ok, I think i'm on the right track, but i'm getting an error in the InStr() line... any idea why?

    <pre>Sub Hide_CRMA()

    Dim i As Long
    Dim j As Long

    For i = 1 To Range("A65536").End(xlUp).Row - 1 Step 1
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">If InStr(Cells.Text(i, 1), "CRMA") Then</span hi>
    For j = 1 To Range("A65536").End(xlUp).Row - 1 Step 1
    If Selection.Cells(i, 1) = "" Then
    Range(i, j).EntireRow.Hidden = True
    End If
    Next j
    End If
    Next i

    End Sub
    </pre>

    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Row Hiding Macro (2k3)

    You probably meant Cells(i, 1).Text instead of Cells.Text(i, 1)

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Row Hiding Macro (2k3)

    You could also use Jan Karel's Autofilter technique in <post#=180,759>post 180,759</post#>, adapted to hide the rows like this:

    Range("A1:A" & ActiveSheet.UsedRange.Rows.Count).AutoFilter Field:=1, Criteria1:="CRMA"
    Range("A2:A" & ActiveSheet.UsedRange.Rows.Count).SpecialCells(xlC ellTypeVisible).Select
    ActiveSheet.AutoFilterMode = False
    Selection.EntireRow.Hidden = True
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Row Hiding Macro (2k3)

    john, honestly i don't see how that would accomplish what i'm doing and i don't know how to modify it to do so - it looks like it would ONLY hide the line with "CRMA" in it, but i need the entire data set hidden. So i've continued to work on this other method just using the for, next loops but its still not working <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    this is what i have now, its still not working and i don't think i have the correct logic yet.

    <pre>Sub Hide_CRMA()

    Dim i As Long
    Dim j As Long

    For i = 1 To Range("A65536").End(xlUp).Row - 1 Step 1
    If InStr(Cells(i, 1).Text, "CRMA") Then
    For j = 1 To Range("A65536").End(xlUp).Row - 1 Step 1
    Rows.Select (j)
    If Cells(j, 1).Text = "" Then
    Range.EntireRow.Hidden = True
    End If
    Next j
    End If
    Next i

    End Sub
    </pre>

    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Row Hiding Macro (2k3)

    If you want help with getting it to work, you are probably going to have to upload a sample workbook that show what your data looks like.
    Legare Coleman

  8. #8
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Row Hiding Macro (2k3)

    i did in my original post, there are 2 tabs - one with the original data set that i have to work with, and a 2nd tab with what i would like it to look like. basically the macro's job is to hide the data sets that contain "CRMA" in the data set title block and then it needs to select the rows under it until it gets to a blank row and hide all that information, and continue onto the rest of the range.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Row Hiding Macro (2k3)

    How about
    <code>
    Sub Hide_CRMA()
    Dim oCell As Range
    For Each oCell In Range(Range("A1"), Range("A65536").End(xlUp))
    If InStr(oCell, "CRMA") Then
    Range(oCell, oCell.End(xlDown)).EntireRow.Hidden = True
    End If
    Next oCell
    End Sub</code>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Row Hiding Macro (2k3)

    Or, slightly better
    <code>
    Sub Hide_CRMA()
    Dim oCell As Range
    For Each oCell In Range(Range("A1"), Range("A65536").End(xlUp))
    If InStr(oCell, "CRMA") Then
    Range(oCell, oCell.End(xlDown).Offset(1, 0)).EntireRow.Hidden = True
    End If
    Next oCell
    End Sub</code>

  11. #11
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Row Hiding Macro (2k3)

    that's oCool... i didn't know about the for each in range logic. i'm going to go write some more macros now <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    thanks, hans!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Row Hiding Macro (2k3)

    Sorry, I missed that. <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

    I see that Hans has beaten me to the solution as usual. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Legare Coleman

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Row Hiding Macro (2k3)

    With that data layout:
    If InStr(oCell, "CRMA") Then
    oCell.CurrentRegion.EntireRow.Hidden = True

    should work.
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Row Hiding Macro (2k3)

    Yep, that's even simpler, but it won't hide the empty row after the currentregion. To remedy this, you could use
    <code>
    With oCell.CurrentRegion
    .Resize(RowSize:=.Rows.Count + 1).EntireRow.Hidden = True
    End With
    </code>
    But then the simplicity is lost again...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •