How to select Rows based on Non empty cells in a column?

[rubenovik]

Hi,

I have a sheet with data in column B that goes from row 5 to row 2000.

Would you be able to help me with a macro that can select the entire rows based on Non empty cells in that column B?

Thanks in advance,

Alexei

 

[Rick Rothstein]

Provided we are talking about entered data and not formulas in Column B, this should select the rows you want...

  On Error Resume Next
  Range("B5:B2000").SpecialCells(xlCellTypeConstants).EntireRow.Select
  On Error GoTo 0

I would mention, though, that it is almost never necessary to select ranges in order to do things with them. If you tell us why you are selecting this rows, perhaps we can show you how to do what you want without the selection part.

 

[rubenovik]

Rick,

Thanks a lot. That little macro did the trick. I appreciate so much your help.

This selection that you helped me to get, is part of a macro that will sort out the final result of a long list of data.
The reason I was asking to select only column B is because in column A and C the data goes a lot longer, sometimes even 10000 rows or more but the data in column B doesnt go beyond 2000. So very ofen after sorting out B the data is not sorted in the order that I need it. With this selection I will be able to sort it out the right way.

Once again, thank you very much for your help.

Alexei

 

[rmccafferty]

I have a similar problem and wonder if this technique would work or if I should alter it some.

I have a group of data that I need to sort with a macro. This "group" is a series of lines of data and the number of lines will vary. I could insist that the user select the rows to sort before running the macro, but I do not know how to designate that selected rows (on screen) as the base data for the sort.

I am thinking that maybe my best resort is to use the code above, use an extra large range like in the above where the range is from rows 5 to 2000, and then use the resulting range for my sort.

It would seem that since the above code selects the data within the macro, it would then be easy to set it as the range to sort.

Does that sound like a good direction to try?