Determining if a Range is Empty

Mr. Tbonz

New Member
Joined
Jan 28, 2012
Messages
27
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.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
=worksheetfunction.counta(range("g4:g17").value) will return 0 iff the the range is empty.
 

Mr. Tbonz

New Member
Joined
Jan 28, 2012
Messages
27
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
 

Mr. Tbonz

New Member
Joined
Jan 28, 2012
Messages
27
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?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,658
Messages
5,597,403
Members
414,142
Latest member
Banyangt

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top