Run-time error '1004': Method 'Range' of object'_Worksheet' Failed

[Yodelayheewho]

I learning Macros and VBA by watching a tutorial video. I got about 1/3 of the way through the video and now I'm stuck.
The screenshot shows the video of the MACRO code and it works fine for the instructor.



I followed the video starting from a blank worksheet. So, I entered the exact same code for the MACRO above (except for the code: .Range("5:84").EntireRow.Hidden = True. My worksheet has more rows to hide.)
I'm getting the error message: Run-time error '1004': Method 'Range' of object' _Worksheet' failed
If I hover over the highlighted text "FirstRow", both say: "FirstRow=-35"
If I hover over the highlighted text ".EntireRow.Hidden = False, it says: ".Range(FirstRow & ":" & Fir... = < Method 'Range' of object '_Worksheet' failed>



I did some research and it was suggested I enter "With Sheet1" prior to the highlighted line of code.
I then get a Compile error: Expected End With



Any thoughts?

 

[vletm]

Yodelayheewho
Have You checked - what is Your SelCol -value?

Compile error: Expected End With
That's as it shows - End With is missing.

You should able to send a sample Excel-file instead Your snapshots.

 

[6StringJazzer]

You not need to add With Sheet1. You already have it, and it's in an appropriate place. (Every time you have a With you have to have a matching End With.)

Your code is different than the original in one important place. The original says
SelCol = ActiveCell.Column
which gets the column number of the current cell. But your code says
SelCol = .Range("B2").Value
which is totally different. What is in cell B2?

When you get the error, go to Debug and hover the mouse over the variable name FirstRow. What is its value? I am guessing it is not a valid row number, like being negative.

 

[Yodelayheewho]

Gosh, sorry about not including a sample. Here it is.

 

[vletm]

Yodelayheewho
Seems You didn't check as written two times...

SelCol seems to be 1
FirstRow = 5 + ((SelCol - 5) * 20) = -75
Negative range ...

 

[Yodelayheewho]

You not need to add With Sheet1. You already have it, and it's in an appropriate place. (Every time you have a With you have to have a matching End With.) Ok, I removed the extra 'With Sheet1'

Your code is different than the original in one important place. The original says
SelCol = ActiveCell.Column
which gets the column number of the current cell. But your code says
SelCol = .Range("B2").Value
which is totally different. What is in cell B2? Sorry, I copied the wrong section of the video. I was much further. This code is correct. The instructor has you create a 'helper' cell in B2. In B2 you can see the column number selected.

When you get the error, go to Debug and hover the mouse over the variable name FirstRow. What is its value? I am guessing it is not a valid row number, like being negative.

Ok, so now it's working. See the latest attached sample.

 

[Yodelayheewho]

Yodelayheewho
Seems You didn't check as written two times...

SelCol seems to be 1
FirstRow = 5 + ((SelCol - 5) * 20) = -75
Negative range ...

Wouldn't 5+ -5 = 0 * 20 = 0, which the instructor said is the correct answer for the FirstRow. I updated the sample. See attached.

 

[vletm]

Yodelayheewho
= 5 + (( 1 - 5 ) * 20)
= 5 + ( -4 * 20 )
= 5 + ( -80 )
= -75
... and FirstRow have to be 1 or more.