Determining if a Range is Empty

[Mr. Tbonz]

I am putting together a spreadsheet and I want to loop through a series of columns (G to L let's say) and in those columns I want to look at a range of rows (4 to 17 let's say). And if that range has no values in it, I want to hide that column and then move on to the next column. I am having a bit of trouble figuring out how to determine if the range is blank and then building that into a loop. Can anybody give me some guidance.

Thanks in advance for the help.

 

[shg]

=worksheetfunction.counta(range("g4:g17").value) will return 0 iff the the range is empty.

 

[Mr. Tbonz]

I need to look at each column individually. If column G is empty, hide just that column then move on to the next column and do the same thing. I am not looking at the the entire range as a whole, just at each individual column.

 

[Jonmo1]

Try

Sub HideCols()
For i = 7 To 12
    If Application.CountA(Cells(4, i).Resize(14, 1)) = 0 Then
        Cells(1, i).EntireColumn.Hidden = True
    End If
Next i
End Sub

 

[Mr. Tbonz]

Try

Sub HideCols()
For i = 7 To 12
    If Application.CountA(Cells(4, i).Resize(14, 1)) = 0 Then
        Cells(1, i).EntireColumn.Hidden = True
    End If
Next i
End Sub

This worked, thank you. Can you explain what the Resize(14,1) is doing?

 

[Jonmo1]

It's still somewhat a hard coded range, but you can add variables in there..

The 14 is 14 rows, Your range from Row 4 to 17 is 14 rows.
So it starts in Cells(4, i) <Row 4, Column i (the variable i from 7 to 12)
So if i = 7, then that is Cell G4

It takes that cell, and resizes it to a range that is 14 rows X 1 column