Confused! about Excel Fun with VBA Excel VBA USERFORMS #2 Sort Database By Employee Id

[Robert Allan]

Confused!

I’m trying to follow along with the Excel VBA USERFORMS #2 Sort Database By Employee Id. I seem to be susessful, except for one part. I have watch it many times, and just don’t understand ( and could not make out what he said) in this part:

If Cells(Rows.Count, 1).End(xlUp).Row = 1 Then

lr = 2

Else

ir =.Cells(Row.Count, 1).End(xlUp).Row

End If

I understand how to get to last row and what these lines mean, but first he types in “lrcode”, and says something that I can’t hear very well. The “ircode” paste the above lines into the sub-routine. Then vanishes. My question is, what is “lrccode” and how does it work.

 

[AlphaFrog]

Just before he types in lrcode, he says he uses Type Pilot. It's a utility running in the background. The user creates a shortcut phrase or keyword (lrcode) and Type Pilot automatically replaces it with a longer phrase e.g. his last row code.

 

[steve the fish]

Plus the code you provided has some errors:

If Cells(Rows.Count, 1).End(xlUp).Row = 1 Then

        lr = 2

    Else

        ir =.Cells(Row.Count, 1).End(xlUp).Row

    End If

You must be using a With statement elsewhere or the .Cells on the 4th row would fail but you dont use it in the 1st row. That should also use .Cells(.Rows. Then your variable changes from lr to ir. They should both be lr no doubt. You also have Row.Count which should be Rows not Row. The statement should therefore be:

If .Cells(.Rows.Count, 1).End(xlUp).Row = 1 Then

        lr = 2

    Else

        lr =.Cells(.Rows.Count, 1).End(xlUp).Row

    End If

You could make this one row with:

lr = Application.Max(2, .Cells(.Rows.Count, 1).End(xlUp).Row)

but you must be wrapping all these with a WITH statement pointing to a worksheet. eg

With Sheets("Sheet1")
'your code
End With

 

[Robert Allan]

Just before he types in lrcode, he says he uses Type Pilot. It's a utility running in the background. The user creates a shortcut phrase or keyword (lrcode) and Type Pilot automatically replaces it with a longer phrase e.g. his last row code.

No longer Confused!

Thanks so much, I kind of thought that that is what he was saying. But when I tried to find something on Type Pilot, I couldn’t. I was looking for something inside of Excel. I had no idea that it did not have anything to do with Excel. Now that I know, and you straighten me out, I got a copy of it and love it. I will probably be using all the time now. I thought maybe it had something to do with my bug, but I was wrong. I’m not going to share my problem was you(I want to work it out myself.), because working out bugs is half the fun of programming. But you was a big help, and I thank you again.