Not understanding VBA Columns reference?

slam

Well-known Member
Joined
Sep 16, 2002
Messages
816
Office Version
  1. 365
  2. 2019
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

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
816
Office Version
  1. 365
  2. 2019
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
816
Office Version
  1. 365
  2. 2019
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
 
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,167,995
Messages
5,856,705
Members
431,828
Latest member
kARTIK12345

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