Select row 1 down to the last row with information

[gberg]

I am looking for a way to select information in Columns A, B & C from row 1 down to the last row with information in these columns.

There is information in Columns A, B and C.
- Many of the rows have information but not all rows, there are many blank rows
- At most the rows with information will only be up to row 5,000
- The number of rows with blanks from the first row to the last row with info will vary greatly from each instance this macro is run

I am trying to select from A1:Cxxxx (Cxxxx being the last row with data in in)
- If there is something in Column A, Column B & C will also have info in it
- If there is nothing in Column A then likewise Column B & C will not have info in it.


I have tried this:

Range("A1:C50000").Select
Range(Selection, Selection.End(xlUp)).Select

This just keeps the A1:C50000 selected.

Hope someone can help

 

[JoeMo]

Range("A1:C" & Cells(rows.count,"C").end(xlup).Row).Select

 

[gberg]

And like that you make it so easy!!!!!!! That works great, thank you for the help.

Greg

 

[My Aswer Is This]

And like that you make it so easy!!!!!!! That works great, thank you for the help.

Greg

If your using this as part of a script your writing. Tell us what else you plan to do. Normally you do not need to select a range. Selecting is common but in most cases not needed.

 

[gberg]

I have many things going on with the script, the selection is needed

 

[Joe4]

I have many things going on with the script, the selection is needed

Most of the time, you actually do not need to select the range in order to work with (you can just reference it).
And the more "Selects" you have in your code, the slower it will run.

 

[My Aswer Is This]

Sort of like this:
Without using select:

Range("A1:C" & Cells(Rows.Count, "C").End(xlUp).Row).Copy Sheets(2).Range("A1")

 

[gberg]

I like the option without the select. Thanks!

 

[Joe4]

Another popular technique is to set a range variable, i.e.

Dim rng as Range
Set rng = Range("A1:C" & Cells(Rows.Count,"C").End(xlUp).Row)

Then you can reference it in other parts of your code easily, i.e.

rng.Copy

or if you wanted to do a bunch of formatting this to the range, you could do them all at once with a "With" statement, i.e.

With rng
    .Font.Bold = True
    .Font.Color = -16776961
End With

This can be a great shortcut if you are referencing this range numerous times in your code.

 

[JoeMo]

And like that you make it so easy!!!!!!! That works great, thank you for the help.

Greg

You are welcome - thanks for the reply.