# Determining if a Range is Empty

#### Mr. Tbonz

##### New Member
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.

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
=worksheetfunction.counta(range("g4:g17").value) will return 0 iff the the range is empty.

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.

Try

Code:
``````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``````

Try

Code:
``````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?

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

Replies
27
Views
851
Replies
6
Views
284
Replies
5
Views
140
Replies
3
Views
187
Replies
4
Views
156

1,203,696
Messages
6,056,764
Members
444,891
Latest member
MelissaBr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back