CODEWITHSUNDEEP
vbaexcel
Scott T
Scott T

Reputation: 233

VBS Excel Trying to select past empty cells in a macro

I'm currently trying to create a macro that will make a selection based on a few criteria. However, I am running into problems when it comes to selecting all data to the right of a current point due to certain cells being empty.

So far I have something like this:

rwcnt = WorksheetFunction.CountA(Range("A:A"))
lastc = Cells(1, Columns.Count).End(xlToLeft).Column
Dim i As Long


For i = 2 To rwcnt
    If IsNumeric(Cells(i, 1).Value) <> True Then
        Range(Cells(i, 1).Address, Cells(i, 1).End(xlDown).Address).Select
        Range(Selection, lastc).Select
        Exit For
    End If
Next

This gives me an error method 'range' of object '_global' failed

previously, I had the last range line reading as:

Range(Selection, Selection.End(xlToRight)).Select

I tried this as I am starting from the left, however, some rows have blank cells which is where the selection would get stopped. Because of this I am trying to make a way to come in from the right and find my last column and then select to that. Any help would be greatly appreciated!

Upvotes: 0

Views: 266

Answers (1)

Jane
Jane

Reputation: 851

Range(Selection, lastc).Select

will fail because for this syntax Range is expecting a starting Range and an ending Range. (See https://msdn.microsoft.com/en-us/library/office/ff841254(v=office.15).aspx for syntax)

lastc is a number (specifically the number of the last used column).

You'd want to use something like the following instead:

        Range(Selection, Cells(i, lastc)).Select

The parameters will depend on exactly what you want to select.

Upvotes: 1

Related Questions