Variable Column range Hidden

JaccoVis90

New Member
Joined
Oct 30, 2015
Messages
34
Hey all,

I have a problem getting a range which is variable to hide
Can you help me

Error 1004, methode Range of object_worksheet failed, ( I THINK, i have the error in dutch)
"fout 1004 vba methode Range van object_worksheet is mislukt"


Code:
Sub test()


Dim startws, invoer As Worksheet
Dim Af2L1L As Integer
Dim Hschuif, Kolstart, Kolend As String
Dim wsb2 As Worksheet
Dim ws As Worksheet
Dim HVVShape, HKooi as String

Set startwb = ThisWorkbook
Set invoer = startwb.Sheets("Input")

Af2L1L = invoer.Range("C1")     'Value in cm
HVVShape = invoer.Range("D5")
HKooi = invoer.Range("F5")
Hschuif = Af2L1L / 5            'Copy shift(per 5 cm)) (BREEDTE, HORIZONTAAL)
Kolstart = 176 - Hschuif        'From were I need to calculate stuff
Kolend = 176 + Hschuif          'To were I need to calculate stuff

naam2 = "VS " & Af2L1L / 100 & "-" & HVVShape / 100 & "-0,9 BV k-" & Hkooi
Set ws = ThisWorkbook.Sheets.Add(After:= _
         ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
ws.Name = naam2
Set wsb2 = startwb.Sheets(naam2)


'HERE IS A LOT OF (UN)IMPORTANT CODING WHICH WORKS (UNIMPORTANT TO THE PROBLEM I HOPE)
'IT COPIES IN A SHEET WITH VALUES WITH DIFFERENT SHIFTED AREAS
'SO I ONLY WANT TO HAVE THE CELLS WITH MULTIPLE VALUES CALCULATED TOGETHER
'SO I CALCULATE THE MEAN, MAX ETC. BETWEEN Kolstart AND Kolend
'THIS WORKS FINE ONLY THE HIDE COLUMNS NOT




wsb2.Range(Cells(1, 2), Cells(1, (Kolstart - 2))).EntireColumn.Hidden = True
wsb2.Range(Cells(1, Kolend + 2), Cells(1, 351)).EntireColumn.Hidden = True




End Sub
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
What is the value of "Kolstart" when you get the error?
If you aren't sure, add a message box prior to the line returning the error, i.e.
Code:
MsgBox Kolstart
Also, do you have any protected cells or merged cells on your sheet?
 
Upvote 0
Try
Code:
With wsb2
   .Range(.Cells(1, 2), .Cells(1, (Kolstart - 2))).EntireColumn.Hidden = True
   .Range(.Cells(1, Kolend + 2), .Cells(1, 351)).EntireColumn.Hidden = True
End With
 
Upvote 0
Try
Code:
With wsb2
   .Range(.Cells(1, 2), .Cells(1, (Kolstart - 2))).EntireColumn.Hidden = True
   .Range(.Cells(1, Kolend + 2), .Cells(1, 351)).EntireColumn.Hidden = True
End With


Thank you,

This works

The strange thing is that when the rest of the code was less it did work (i tested it and made a note of it that it worked)

What can be the reason of this?
 
Upvote 0
See if Fluff's code resolves your issue.
 
Upvote 0
Thank you Fluff, your code works

Can you give me an explaination why the other didn't work and your did.
So I can learn from it for the future
 
Upvote 0
The part in red
Code:
wsb2.Range([COLOR=#ff0000]Cells(1, 2), Cells(1, (Kolstart - 2))[/COLOR]).EntireColumn.Hidden = True
is looking at the activesheet rather than wsb2.
So if wsb2 is the activesheet your code will work, otherwise you will get the error.
What you need to do is qualify the range & the cells so that they are looking at the same sheet. One way to do that is
Code:
wsb2.Range(wsb2.Cells(1, 2), wsb2.Cells(1, (Kolstart - 2))).EntireColumn.Hidden = True
Or you can use the shorthand version using the With Statement
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

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