Some macro help - working with pre-selected rows

  • Thread starter Thread starter AlanN
  • Start date Start date
A

AlanN

Lets say I have selected the arbitray range of j31:ax44. I want to run some
macro code that will hide every column in that selection where the entire
selected column range has nothing in it (blank). Data validation may be
assigned to the cells, but if nothing has been selected from the drop down
list, then those cells are technically blank. There may be data entered
elsewhere in the column, but it is outside of the selected range and thus
shouldn't be considered for being hidden or not. Can anyone help me with
some code to perform this action?

TIA, AlanN
 
How about:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCol As Range

Set myRng = Selection

For Each myCol In myRng.Columns
If Application.Count(myCol) = 0 Then
myCol.Hidden = True
Else
myCol.Hidden = False
End If
Next myCol

End Sub

Select your range and then try it out.
 
I put some characters (letters) into some cells and then selected a range
encompassing those cells. When I ran the macro, it hid all the columns in
that range including those that I had keyed characters into.
Any ideas?
 
Typo.

Change:
If Application.Count(myCol) = 0 Then
to:
If Application.CountA(myCol) = 0 Then

=Count() will look only for numbers.
=counta() will look for anything (including formulas that evaluate to "").
 
Bingo! That does the trick.

Thanks, Alan

Dave Peterson said:
Typo.

Change:
If Application.Count(myCol) = 0 Then
to:
If Application.CountA(myCol) = 0 Then

=Count() will look only for numbers.
=counta() will look for anything (including formulas that evaluate to "").
Click to expand...
 
Back
Top