Solved VBA to hide rows that are null after a word

Microsoft Excel 2010 - complete product...
April 30, 2015 at 11:50:13
Specs: Windows 10
Hi,

I have a worksheet that has multiple sections that contain information pertaining to a specific project. I would like to be able to add a macro to a command button that would look for the text "Job #:" in column A and if the cell in column b is blank in that same row, I would like to have it hide all the rows until it finds the word "Subtotal" in column G.

Any help is appreciated.....Thank you,
Sandi

message edited by SandiS


See More: VBA to hide rows that are null after a word

Report •


#1
April 30, 2015 at 12:23:43
Help me understand your requirements...

Will there be more than one occurance of this situation or are you just trying to hide one set of rows?

In other words, is this a possibility? In this example, Rows 4, 5, 10 & 11 should be hidden, correct?

      A           B         ...           G
1    Data       Data     
2    Data       Data 
3    Job #
4    Data       Data 
5    Data       Data 
6                                     Sub Total
7    Data       Data      
8    Data       Data 
9    Job #
10   Data       Data 
11   Data       Data 
12                                    Sub Total

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 30, 2015 at 12:31:15
That is almost exactly it but there will be only one occurrence with word Subtotal, there would be multiple for job # but we would basically look for the first occurrence that is blank and hide all beneath until the word Subtotal (All rows underneath the 1st will all be blank). So we would hide rows 3 thru 11.

      A           B         ...           G
1    Data       Data     
2    Data       Data 
3    Job #
4    Data       Data 
5    Data       Data 
6                                     
7    Data       Data      
8    Data       Data 
9    Job #
10   Data       Data 
11   Data       Data 
12                                    Sub Total


Report •

#3
April 30, 2015 at 13:36:00
✔ Best Answer
Try this code...

Sub HideBlankJobs()
'Unhide all rows (mainly for testing, feel free to delete)
   Rows(1 & ":" & Rows.Count).Hidden = False
'Determine which Row in Column G contains "Sub Total")
    With Columns(7)
      Set st = .Find("Sub Total", LookIn:=xlValues, Lookat:=xlPart)
    End With
'Search Column A for "Job #"
  With Columns(1)
    Set j = .Find("Job #", LookIn:=xlValues, Lookat:=xlPart)
'When "Job #" is found...
    If Not j Is Nothing Then
        firstAddress = j.Address
        Do
'Determine if Column B is empty
         If Range("B" & j.Row) = "" Then
'If Column B is empty, hide Rows between "Job #" and "Sub Total"
              Rows(j.Row + 1 & ":" & st.Row - 1).Hidden = True
         End If
'If Column B is not empty, find next "Job #"
          Set j = .FindNext(j)
        Loop While Not j Is Nothing And j.Address <> firstAddress
    End If
  End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 30, 2015 at 13:50:11
I am getting an 'Object variable or with block variable not set" error on this line

  Rows(j.Row + 1 & ":" & st.Row - 1).Hidden = True


Report •

#5
April 30, 2015 at 13:52:29
Disregard previous message I noticed Sub Total had a space in and I did not have one, works perfectly now :)

Thank you very much!!!!
Sandi


Report •

#6
April 30, 2015 at 13:53:32
I just want to add you are amazing at what you do and I appreciate the assistance as I stumble along :)

Sandi


Report •


Ask Question