0

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!

shA.t
  • 16,580
  • 5
  • 54
  • 111
Scott T
  • 233
  • 2
  • 5
  • 14
  • 1
    What are you trying to do? End up at a cell, select that, then exit the for() loop? Once you hit a non-numeric cell, it's going to exit that loop. Where does your Range error come in? Your lastc, as far as I can tell, is going to keep getting sent to column 1. You're starting at the last column (`.columns.count`), and going to the left until an empty cell. Is there an empty cell in row 1 before getting back to A1? – BruceWayne Jul 20 '15 at 17:35
  • @ScottT Could you indent the first line of code please (add 4 spaces to the start of the line)? This will allow it to be formatted correctly. Unfortunately I can't make an edit with so few characters changed. – Jane Jul 20 '15 at 17:38
  • I'm confused what you're trying to do - Seems like it should be easy, but your description of what you're trying to accomplish isn't making sense to me... – John Bustos Jul 20 '15 at 17:46
  • Thanks for the help, everyone! – Scott T Jul 20 '15 at 17:51
  • See [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) for methods on getting away from relying on select and activate to accomplish your goals. –  Jul 20 '15 at 17:54

1 Answers1

1
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.

Jane
  • 851
  • 4
  • 9