Not understanding VBA Columns reference?

slam

Well-known Member
Joined
Sep 16, 2002
Messages
779
I have this piece of code to hide some rows which works perfectly:

Code:
If Not Intersect(Target, Range("B3")) Is Nothing And [B3] >= 1 And [B3] <= 40 Then
    With wsRecords
        .Unprotect "password"
        wsRecords.Rows("3:42").Hidden = True
        wsRecords.Rows("3:" & Target.Value + 2).Hidden = False
    End With
End If
I tried converting it to hide some columns on another worksheet and I'm getting a run time error 1004 application defined or object defined error on the bolded line

Code:
If Not Intersect(Target, Range("B3")) Is Nothing And [B3] >= 1 And [B3] <= 40 Then
    With wsDrivers
        .Unprotect "password"
[B]        wsDrivers.Columns("6:45").Hidden = True[/B]
        wsDrivers.Columns("6:" & Target.Value + 5).Hidden = False
    End With
End If

What am I doing wrong?

Thanks
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

sericom

Well-known Member
Joined
Jan 19, 2006
Messages
915
Code:
wsDrivers.Columns("F:AS").Hidden = True

or using numbers

Code:
wsDrivers.Columns(6).Resize(, 40).Hidden = True
 
Last edited:

slam

Well-known Member
Joined
Sep 16, 2002
Messages
779
Code:
[B]wsDrivers.Columns("F:AS").Hidden = True[/B]

Here I was thinking I had to do number references for columns..... letters is a lot easier!

However, if I change the next row to letter references, I get a type mismatch, but if I don't, it doesn't work.
 

sericom

Well-known Member
Joined
Jan 19, 2006
Messages
915
That's why I gave you 2 examples, just change the 40 in Resize to your variable.
 

slam

Well-known Member
Joined
Sep 16, 2002
Messages
779
That's why I gave you 2 examples, just change the 40 in Resize to your variable.

Thank you! Didn't see that you'd edited your post. This works perfectly, and is even simpler than before.

Code:
If Not Intersect(Target, Range("B3")) Is Nothing And [B3] >= 1 And [B3] <= 40 Then
    With wsDrivers
        .Unprotect "password"
        wsDrivers.Columns(6).Resize(, 40).Hidden = True
        wsDrivers.Columns(6).Resize(, Target.Value).Hidden = False
    End With
End If
 

Watch MrExcel Video

Forum statistics

Threads
1,123,402
Messages
5,601,475
Members
414,452
Latest member
Dannysamworth

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