1

Okay, let me start by saying I am a noob at this. I'm following this step-by-step video, and I still found a way to mess it up somehow. I've tried reading related questions, but I don't really know much about VBA to make sense of the answers.

Basically, I'm trying (and failing) to create an "Employee Manager." At around 9:44 of the video, the narrator said we'll need to create a macro to hide and show specific cells depending on what tab we click on.

Here is my code for Sheet1:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Not Intersect(Target, Range("E4:H4")) Is Nothing Then
       Range("B2").Value = Target.Column
       Range("F2").Select
       SwitchHTabs
    End If
End Sub

And I have this code under modules, with the macro named SwitchHTabs:

Option Explicit

Sub SwitchHTabs()
    Dim SelCol As Long
    Dim FirstRow As Long

    SelCol = ActiveCell.Column

    With Sheet1
        .Range("5:84").EntireRow.Hidden = True
        FirstRow = 5 + ((SelCol - 5) * 20)
        .Range(FirstRow & "." & FirstRow + 19).EntireRow.Hidden = False 'This is the line with the error
    End With
End Sub

This error pops up when I try to run the code:
enter image description here

And when I click Debug, it highlights this line of code:
enter image description here

Where'd I mess up? I'm using Excel 2016.

Improve this question
0

2 Answers 2

Reset to default
2

Compare your two Range lines in that sub:

.Range("5:84").EntireRow.Hidden = True 'Works
.Range(FirstRow & "." & FirstRow + 19).EntireRow.Hidden = False 'Doesn't Work

For the sake of argument, let's assume that FirstRow is 1, and then process that second line of code:

.Range("5:84").EntireRow.Hidden = True 'Works
.Range("1.20").EntireRow.Hidden = False 'Doesn't Work

Hopefully it is now easier to see that you have used a Full-Stop in place of a Colon.

(As an aside - you can use .Rows("5:84").Hidden instead to remove the need for .EntireRow)

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

O. M. F. G. I can't believe I missed that, you're a lifesaver! Thank you, thank you, thank you!!!
-1

.Range("C;BE").EntireColumn.Hidden = True .Range("C;BE").Columns.Hidden = True both not working

Improve this answer

2 Comments

method 'Range' of object '_worksheet' failed
Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.