CODEWITHSUNDEEP
excelvba
JOE SKEET
JOE SKEET

Reputation: 8118

selecting a row in vba problem

i am programmatically selecting a row in VBA but it is giving me a TYPE MISMATCH error on this:

Rows(Str(i) & ":" & Str(i)).Select

what am i doing wrong?

For i = 5 To 1000
    If Worksheets("5470").Cells(i, 2) = "" Then
        Rows(Str(i) & ":" & Str(i)).Select
        Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
        Selection.Delete Shift:=xlUp
        Exit For
    End If
Next i

Upvotes: 3

Views: 63247

Answers (2)

MarcelDevG
MarcelDevG

Reputation: 1372

The error you are getting is because the STR function prepends a space to the number. So, when i=100, you'll get " 100: 100". You could the method of GolezTrol, or use cstr() instead of str(). The space is prepended to account for a possible negative value.

Upvotes: 5

GolezTrol
GolezTrol

Reputation: 116180

Why not use 

Rows(i).Select

Afaik, rows can be indexed by the row number too.

Alternative:

Cells(i, 1).EntireRow.Select

Upvotes: 10

Related Questions