How do I skip some lines of code based on whether I have selected a cell in Row3 or not?

Question

I am trying to have a spreadsheet where the user selects any cell and then runs this macro. If the cell is not in Row 3 then select the entire row that that cell is in, cut and insert that row in Row 3. (this part works fine) If the selected cell is in Row 3 then I want to skip the cut and insert part of the code. This seems like it should be simple to do but I get the error: End if without block if, when I run this macro. Any help would be greatly appreciated. Thank you.

Dim Row As String

ActiveCell.EntireRow.Select

strRow = Selection


If Selection = Rows("3:3") Then GoTo SkipToHere
End If

Selection.Cut
    ActiveWindow.SmallScroll Down:=-84
    Rows("3:3").Select
    Selection.Insert Shift:=xlDown
    Range("A3").Select



SkipToHere:

Generally any form of `Selecting` in vba usually is bad, but for your current situation it _may be slightly okay_ with emphasis on slightly. Read [this](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). — chancea, Jan 09 '15 at 20:25

score 2 · Answer 1 · answered Jan 09 '15 at 20:18

In VBA, you can write IF statements in multiple ways: 1. All on one line, or 2. With an Else component, or 3. using ElseIf ... etc.

When you write an IF statement like this:

If A=True Then B

with no Else component, then VBA does not require an End IF statement. It is needed when you use IF like this:

IF A=True Then
  Do this
Else
  Do Something else
End If

score 1 · Accepted Answer · answered Jan 09 '15 at 20:24

Using GoTo in this context is probably not the best way to go about this. Instead I would just encase your first part in an If:

Dim strRow As String

ActiveCell.EntireRow.Select

strRow = Selection

If Selection.Row <> 3 Then

    Selection.Cut
    Rows("3:3").Insert Shift:=xlDown
    Range("A3").Select

End If

You should also as a matter of principle avoid .Select where you do not need it.

score 0 · Answer 3 · answered Jan 09 '15 at 20:18

0

Dim Row As String

If Activecell.Row = 3 Then GoTo SkipToHere
End If

ActiveCell.EntireRow.Select
Selection.Cut
ActiveWindow.SmallScroll Down:=-84
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range("A3").Select



SkipToHere:

answered Jan 09 '15 at 20:18

bilbo_strikes_back

591
3
10

score 0 · Answer 4 · answered Jan 09 '15 at 20:40

Sub Horsten()

' Do the selection and the third row have nothing in commen?

If Intersect(Selection, Rows(3)) Is Nothing Then

' Then cut and paste (but without changing the selection)

    Selection.EntireRow.Cut
    Rows(3).Insert Shift:=xlDown

' Then adjust the positioning if you like

    ActiveWindow.SmallScroll Up:=84
    Cells(3, 1).Select
End If

End Sub

How do I skip some lines of code based on whether I have selected a cell in Row3 or not?

4 Answers4