Selecting rows based on criteria

Hi,

I am relatively new to macros, and need help with regard to selecting rows
in Excel.

1. I currently have a macro which sorts a pre-defined range of 100 rows.
However there is only data in about 50 of these rows (although this exact
figure varies. On certain days there will be 60 rows, but never more than 100
rows).

2. The macro then copies the sort range and pastes the values elsewhere.

The problem is that one of the sort criteria is a field that contains both
numbers and text. So the result of the macro is (e.g.) 25 rows of numbers,
then 50 rows of blank space, and then 25 rows of text.

What I need the macro to do is (in step 2. above) only select rows that
contain data, so that the pasted values data will only contain 50 rows of
data, rather than 100.

I am sure there is an easy way to do this using countloop or something, but
I am not experienced to know how to use this.

Any suggestions would be much appreciated.

Thanks

if you sort ascending - numbers come before text come before blanks, I just tried it.

hi,
selecting row that only contain data.
I am assuming that you have a solid block of data with no blank rows and
your data start in cell A1.
note: if your are adding new and unfamiliar code, it is always a good idea
to create a test module and play with the new code to make sure that it is
doing what you want before adding it to your macro perticularly if you have
to modify the new code.

remove the copy range sniplet from you code and insert this instead.

Range(Range("A65500").End(xlUp), _
Range("A65500").End(xlUp).End(xlUp).Offset(0, 2)).Select
Selection.copy

This will select only the rows that have data regardless of how many rows.
the above sniplet only select the first 3 columns so you will need to change
the offset to the number of columns you have minus 1. the first number is
rows, second is columns. if you have 10 columns then offset(0, 9).
robert111 is right about the sort of numbers and text. not much you can do
about that.
if you want to resize the range each time you run the code add this after
the above sniplet.

ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:= _
Selection

you will have to change the name from MyRange to your range name.

good luck
FSt1


"JCP" wrote:

> Hi,
>
> I am relatively new to macros, and need help with regard to selecting rows
> in Excel.
>
> 1. I currently have a macro which sorts a pre-defined range of 100 rows.
> However there is only data in about 50 of these rows (although this exact
> figure varies. On certain days there will be 60 rows, but never more than 100
> rows).
>
> 2. The macro then copies the sort range and pastes the values elsewhere.
>
> The problem is that one of the sort criteria is a field that contains both
> numbers and text. So the result of the macro is (e.g.) 25 rows of numbers,
> then 50 rows of blank space, and then 25 rows of text.
>
> What I need the macro to do is (in step 2. above) only select rows that
> contain data, so that the pasted values data will only contain 50 rows of
> data, rather than 100.
>
> I am sure there is an easy way to do this using countloop or something, but
> I am not experienced to know how to use this.
>
> Any suggestions would be much appreciated.
>
> Thanks
>

Hi,

Unfortunately, although the block of data I wish to copy contains no blank
rows, the xlEnd will not work as beneath the block of data I want to copy are
other rows with formulae in.

So I am still stuck!

"FSt1" wrote:

> hi,
> selecting row that only contain data.
> I am assuming that you have a solid block of data with no blank rows and
> your data start in cell A1.
> note: if your are adding new and unfamiliar code, it is always a good idea
> to create a test module and play with the new code to make sure that it is
> doing what you want before adding it to your macro perticularly if you have
> to modify the new code.
>
> remove the copy range sniplet from you code and insert this instead.
>
> Range(Range("A65500").End(xlUp), _
> Range("A65500").End(xlUp).End(xlUp).Offset(0, 2)).Select
> Selection.copy
>
> This will select only the rows that have data regardless of how many rows.
> the above sniplet only select the first 3 columns so you will need to change
> the offset to the number of columns you have minus 1. the first number is
> rows, second is columns. if you have 10 columns then offset(0, 9).
> robert111 is right about the sort of numbers and text. not much you can do
> about that.
> if you want to resize the range each time you run the code add this after
> the above sniplet.
>
> ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:= _
> Selection
>
> you will have to change the name from MyRange to your range name.
>
> good luck
> FSt1
>
>
> "JCP" wrote:
>
> > Hi,
> >
> > I am relatively new to macros, and need help with regard to selecting rows
> > in Excel.
> >
> > 1. I currently have a macro which sorts a pre-defined range of 100 rows.
> > However there is only data in about 50 of these rows (although this exact
> > figure varies. On certain days there will be 60 rows, but never more than 100
> > rows).
> >
> > 2. The macro then copies the sort range and pastes the values elsewhere.
> >
> > The problem is that one of the sort criteria is a field that contains both
> > numbers and text. So the result of the macro is (e.g.) 25 rows of numbers,
> > then 50 rows of blank space, and then 25 rows of text.
> >
> > What I need the macro to do is (in step 2. above) only select rows that
> > contain data, so that the pasted values data will only contain 50 rows of
> > data, rather than 100.
> >
> > I am sure there is an easy way to do this using countloop or something, but
> > I am not experienced to know how to use this.
> >
> > Any suggestions would be much appreciated.
> >
> > Thanks
> >