VBA - If all cells in range have value="" then....

Fishboy

Well-known Member
Joined
Feb 13, 2015
Messages
4,267
Hi all,

I am trying to avoid writing hundreds of specific lines of code and am looking for an efficient solution if possible.

In my Excel 2010 I have range C48:C167 that are all populated with IF formulas which will either return a value if one exists or return "" if not. I need a macro which checks the values in the range and, if all values are "", then row 47 needs to be hidden. If there is so much as a single cell in the range with a value other than "", row 47 needs to be unhidden.

So far I cannot seem to find the right syntax to get this working, short of having something ridiculous like

Rich (BB code):
If Worksheets("Generator").Range("C48").Value = "" And Range("C49").Value = ""......and so on
from 48 to 167.

Can anyone offer any suggestions please?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Actually as an additional query, I also have a ridiculously long macro which also checks each line and if the cell in column C is blank it hides the individual row. Is there a better way of doing this than having 200 lines of code? Please see my example below which is currently copy / pasted 200+ times with incrementing row numbers

Code:
If Worksheets("Generator").Range("C48") = "" Then Rows("48:48").Hidden = True
If Worksheets("Generator").Range("C48") <> "" Then Rows("48:48").Hidden = False
 
Upvote 0
You can loop through them, for each cell in range, if cell.value = "", then cell.entirerow.hidden = true

or have the formula return an error and use the specialcells method (either on the worksheet or in the code) to select and hide
 
Upvote 0
Actually as an additional query, I also have a ridiculously long macro which also checks each line and if the cell in column C is blank it hides the individual row. Is there a better way of doing this than having 200 lines of code? Please see my example below which is currently copy / pasted 200+ times with incrementing row numbers

Code:
If Worksheets("Generator").Range("C48") = "" Then Rows("48:48").Hidden = True
If Worksheets("Generator").Range("C48") <> "" Then Rows("48:48").Hidden = False

You can use the specialcells method for this too
 
Upvote 0
You can use the specialcells method for this too

Hi Sheetspread, thanks for the suggestion.

I must confess I haven't actually ever written a looping macro yet, nor am I familiar with specialcells. Could you possibly give me some example code to work with that I might try and doctor to suit my needs please?
 
Upvote 0

Excel 2010
ABCDEFGHIJ
12211017361
2106122610795
398103123767
45765948598
565165661105
667927271
78564634567
8611071710622
91103921036810
1063110922107
111067210310163
1241033246744
13581749843
1463458781087
1521043262691
1688872210959
1710468356436
18110345618210
1975381510522
20431074559
2178211062279
Sheet3 (3)


Code:
Sub hideblanks()
For Each cell In Range("A1:J21")
If cell.Value = "" Then
cell.EntireRow.Hidden = True
End If
Next cell
End Sub


Excel 2010
ABCDEFGHIJ
2106122610795
398103123767
45765948598
565165661105
78564634567
8611071710622
91103921036810
111067210310163
1241033246744
1463458781087
1521043262691
1688872210959
1710468356436
18110345618210
1975381510522
2178211062279
Sheet3 (3)



or:



Excel 2010
ABCDEFGHIJ
12211017361
2106122610795
398103123767
45765948598
565165661105
667927271
78564634567
8611071710622
91103921036810
1063110922107
111067210310163
1241033246744
13581749843
1463458781087
1521043262691
1688872210959
1710468356436
18110345618210
1975381510522
20431074559
2178211062279
Sheet3 (3)


Code:
Sub hiderowsspecialcells()
Range("a1:j21").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End Sub


Excel 2010
ABCDEFGHIJ
2106122610795
398103123767
45765948598
565165661105
667927271
78564634567
8611071710622
91103921036810
111067210310163
1241033246744
1463458781087
1521043262691
1688872210959
1710468356436
18110345618210
1975381510522
2178211062279
Sheet3 (3)


Note that only the real blank cells will be hidden by the second code, not = "" cells, which is why you should return a different string that can be distinguished (or use the filter)
 
Upvote 0
Thanks mate. I will have a play around with this and get it tweaked to my data.

Cheers!
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

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
Back
Top