Can't change the range

listerheb

Active Member
Joined
Aug 22, 2003
Messages
262
Hi I have this macro which I found while searching the board.

it works but I am having trouble changing it to meet my needs.
Sub HideColumns()

Dim i As Integer
On Error Resume Next
Application.ScreenUpdating = False
With Range("d2:Iq2")
.EntireColumn.Hidden = False
For i = 1 To .Columns.Count
If .Columns(i).Value = "" Then
.Columns(i).EntireColumn.Hidden = True
End If
Next i
End With
Application.ScreenUpdating = True
End Sub

seems to work fine in hiding the columns that have nothing in D
but as soon as I change it to my range which is d2:iq40 it stops working properly.
Can anyone help me change it so that it will hide columns only when there is no data in D2:IQ40
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
See if this gets you closer to what you need.

Sub HideColumns()
Application.ScreenUpdating = False
Range("D:IQ").EntireColumn.Hidden = False
Dim i As Integer
For i = 4 To 251
If Application.CountA(Range(Cells(2, i), Cells(40, i))) = 0 Then Columns(i).EntireColumn.Hidden = True
Next i
Application.ScreenUpdating = True
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,101
Messages
5,768,101
Members
425,453
Latest member
bince

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