If and ELseIF

[imback2nite]

I've written this and don't know what is going wrong.

Private Sub CommandButton3_Click()
        If Range("3:2544").EntireRow.Hidden = False Then
        MsgBox ("Select Which Week To Update Names")
            Exit Sub
       ElseIf Range("4:6").EntireRow.Hidden = False Then
            Range("B8:B47").ClearContents
            Worksheets("Names").Range("B2:B41").Copy
            Range("B8").PasteSpecial Paste:=xlPasteValues
        ElseIf Range("51:54").EntireRow.Hidden = False Then
            Range("B56:B95").ClearContents
            Worksheets("Names").Range("B2:B41").Copy
            Range("B56").PasteSpecial Paste:=xlPasteValues
       ElseIf Range("99:102").EntireRow.Hidden = False Then
            Range("B104:B143").ClearContents
            Worksheets("Names").Range("B2:B41").Copy
            Range("B104").PasteSpecial Paste:=xlPasteValues
       ElseIf Range("147:150").EntireRow.Hidden = False Then
            Range("B152:B191").ClearContents
            Worksheets("Names").Range("B2:B41").Copy
            Range("B152").PasteSpecial Paste:=xlPasteValues
        End If
            Application.CutCopyMode = False
        Range("B1").Select
    Application.ScreenUpdating = True
End Sub

It all works except for the

       ElseIf Range("4:6").EntireRow.Hidden = False Then
            Range("B8:B47").ClearContents
            Worksheets("Names").Range("B2:B41").Copy
            Range("B8").PasteSpecial Paste:=xlPasteValues

The rest of the ElseIf's work fine. I tried deleting the code below and it works fine but I do need it. Any help wound be appreciated.

 If Range("3:2544").EntireRow.Hidden = False Then
        MsgBox ("Select Which Week To Update Names")
            Exit Sub

[/CODE] Because the code works fine without it.

 

[ExcelToDAX]

what is the error that you are getting?

 

[imback2nite]

what is the error that you are getting?

No error message. This code just refuses to work. All the other ElseIf statements work.

       ElseIf Range("4:6").EntireRow.Hidden = False Then
            Range("B8:B47").ClearContents
            Worksheets("Names").Range("B2:B41").Copy
            Range("B8").PasteSpecial Paste:=xlPasteValues

Although the below code executes even if the Range ('4:6').EntireRow Hidden = False is Satisfied. Is it a syntax error, like ElseIf should be another IF statement? I did try that also. No joy. Sidenote: If I remove the code below and change the ElseIf on the above code, it works just fine. But like I said before I do need the below code.

If Range("3:2544").EntireRow.Hidden = False Then
        MsgBox ("Select Which Week To Update Names")

 

[Alex Blakenburg]

For your code to run the ElseIf Range("4:6").EntireRow.Hidden = False section, the following conditions need to be met:
• Row 3 is hidden
• Row 4 is visible
Is that the case ?

Note: All your visible row checks only check the first row in the range you are giving it eg 3:2544 only looks at row 3 and 4:6 only looks at row 4

 

[imback2nite]

For your code to run the ElseIf Range("4:6").EntireRow.Hidden = False section, the following conditions need to be met:
• Row 3 is hidden
• Row 4 is visible
Is that the case ?

Note: All your visible row checks only check the first row in the range you are giving it eg 3:2544 only looks at row 3 and 4:6 only looks at row 4

 

[imback2nite]

That's right. It was ("3:6") but I changed it to ("4:6") just in cast that was the problem. It wasn't and I forgot to change it back.
I've been trying to see if the syntax is correct. Like on the third line I have Exit Sub if the condition is not met. I tried using just IF instead of ElseIf after that. Not working.

 

[imback2nite]

That's right. It was ("3:6") but I changed it to ("4:6") just in cast that was the problem. It wasn't and I forgot to change it back.
I've been trying to see if the syntax is correct. Like on the third line I have Exit Sub if the condition is not met. I tried using just IF instead of ElseIf after that. Not working.

Okay so I changed the 'Exit Sub' to 'End If' and started the next line with 'If'. It's working but I'm still getting the MsgBox only for the ("3:6") area. But the ("3:6") rows are not hidden.

        If Range("3:2544").EntireRow.Hidden = False Then
        MsgBox ("Select Which Week To Update Names")
            End If
        If Range("3:6").EntireRow.Hidden = False Then
            Range("B8:B47").ClearContents
            Worksheets("Names").Range("B2:B41").Copy
            Range("B8").PasteSpecial Paste:=xlPasteValues
        ElseIf Range("51:54").EntireRow.Hidden = False Then
            Range("B56:B95").ClearContents
            Worksheets("Names").Range("B2:B41").Copy
            Range("B56").PasteSpecial Paste:=xlPasteValues
       ElseIf Range("99:102").EntireRow.Hidden = False Then
            Range("B104:B143").ClearContents
            Worksheets("Names").Range("B2:B41").Copy
            Range("B104").PasteSpecial Paste:=xlPasteValues
       ElseIf Range("147:150").EntireRow.Hidden = False Then
            Range("B152:B191").ClearContents
            Worksheets("Names").Range("B2:B41").Copy
            Range("B152").PasteSpecial Paste:=xlPasteValues
        End If
            Application.CutCopyMode = False
        Range("B1").Select
    Application.ScreenUpdating = True
End Sub

 

[Alex Blakenburg]

If Row 3 is visible then in your latest code it should display the msgbox then perform the first part of the If statement.
Have you used F8 to step through it and follow it through the code ?

 

[imback2nite]

If Row 3 is visible then in your latest code it should display the msgbox then perform the first part of the If statement.
Have you used F8 to step through it and follow it through the code ?

I just checked the change that I made and it's now disregarding the beginning 'If' statement completely.

 If Range("3:2544").EntireRow.Hidden = False Then
        MsgBox ("Select Which Week To Update Names")
            End If

Also if all the rows are not hidden it only follows

        If Range("3:6").EntireRow.Hidden = False Then
            Range("B8:B47").ClearContents
            Worksheets("Names").Range("B2:B41").Copy
            Range("B8").PasteSpecial Paste:=xlPasteValues

I did try using the F8 key. Here it works fine. smh

 

[Alex Blakenburg]

Can I just confirm that you understand that ElseIf statements are alternatives. By that I mean when the code reaches an ElseIf that is True then it executes that section of code and from there goes straight to End If, bypassing all the ElseIfs that follow.