Amend code to do nothing if cells in a range are empty

**nickmax1** · 02-05-2015, 05:47 AM

I have a form that displays a list of items as per the users request and it works very well for me - i just need a little help with the following: The code below hides data in my sheet that the user is not interested in - what I want the code to do though, is IF there is no value in the range specified then do NOTHING. I imagine there needs to be an IF statement in my code below, something along the lines of IF Rng <> "" then ......

If ComboBox1.Value = "Company" Then
        'Application.ScreenUpdating = False
         Set rng = Range("B193", "B250")
         For Each cel In rng
         cel.EntireRow.Hidden = False
         Next cel
         For Each cel In rng
         If Not cel.Value = "Company" Then
        cel.EntireRow.Hidden = True
         End If
         Next cel
    End If

**:) Sixthsense :)** · 02-05-2015, 05:55 AM

Instead of looping try it in this way...

Dim rng As Range, rng1 As Range, rng2 As Range

If ComboBox1.Value = "Company" Then
    Set rng = Range("B193", "B250")
    rng.EntireRow.Hidden = True
    On Error Resume Next
        rng1 = rng.SpecialCells(xlCellTypeConstants)
        rng2 = rng.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    If Not rng1 Is Nothing Then rng1.EntireRow.Hidden = False
    If Not rng2 Is Nothing Then rng1.EntireRow.Hidden = False
End If

**nickmax1** · 02-05-2015, 06:17 AM

Sixthsense,
thanks for the quick reply but that doesnt work, your code doesnt take into account that its looking for the word Company in the range, so your code hides it all?

**:) Sixthsense :)** · 02-05-2015, 06:26 AM

My code just hiding blank cells in your range

**nickmax1** · 02-05-2015, 06:34 AM

hmm, not sure why then but when i use your code it hides everything. I have lots of values in my range 193-250 but they are all hidden.

Besides, i think even if it did work, it is still ignoring the fact that i want it to hide all rows that dont have the word "Company" in the range.

**:) Sixthsense :)** · 02-05-2015, 06:45 AM

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**nickmax1** · 02-05-2015, 07:38 AM

try this

though i just noticed my SORT HERE button doesnt work in my amended workbook - will look into it but you can see the data I am working with nonetheless