Moving to next row below a selected range

In Excel 2002, I need a Macro that will select a range containing data in a
spicific column, then move down one row belosw the rand and select all rows
from that point to bottom of worksheet and delete those rows. current my
Macro has the following lines:
Sheets("Supplier").Select
Range("A1:R220").Sort Key1:=Range("C2"), Order1:=xlAscending,
Key2:=Range _
("D2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Range("C1").Select
Selection.End(xlDown).Select
(XXXXXXXXXXX)
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Range("A2").Select
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(5), _
Replace:=True, PageBreaks:=True, SummaryBelowData:=True
ActiveWindow.SelectedSheets.PrintPreview

In place of the (XXXXXXXXXX), I need a command that will move down one row
and then continue with the rest of the macro.

Selection.Offset(1, 0).Select

"Loris" wrote:

> In Excel 2002, I need a Macro that will select a range containing data in a
> spicific column, then move down one row belosw the rand and select all rows
> from that point to bottom of worksheet and delete those rows. current my
> Macro has the following lines:
> Sheets("Supplier").Select
> Range("A1:R220").Sort Key1:=Range("C2"), Order1:=xlAscending,
> Key2:=Range _
> ("D2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
> MatchCase _
> :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
> DataOption2:=xlSortNormal
> Range("C1").Select
> Selection.End(xlDown).Select
> (XXXXXXXXXXX)
> Range(Selection, Selection.End(xlDown)).Select
> Selection.Delete Shift:=xlUp
> Range("A2").Select
> Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(5), _
> Replace:=True, PageBreaks:=True, SummaryBelowData:=True
> ActiveWindow.SelectedSheets.PrintPreview
>
> In place of the (XXXXXXXXXX), I need a command that will move down one row
> and then continue with the rest of the macro.
>

selection.Offset(1,0).Select

--
Regards,
Tom Ogilvy

"Loris" <Loris@discussions.microsoft.com> wrote in message
news:23540564-FAE6-48B8-B33E-DF27CFB8C76A@microsoft.com...
> In Excel 2002, I need a Macro that will select a range containing data in
a
> spicific column, then move down one row belosw the rand and select all
rows
> from that point to bottom of worksheet and delete those rows. current my
> Macro has the following lines:
> Sheets("Supplier").Select
> Range("A1:R220").Sort Key1:=Range("C2"), Order1:=xlAscending,
> Key2:=Range _
> ("D2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
> MatchCase _
> :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
> DataOption2:=xlSortNormal
> Range("C1").Select
> Selection.End(xlDown).Select
> (XXXXXXXXXXX)
> Range(Selection, Selection.End(xlDown)).Select
> Selection.Delete Shift:=xlUp
> Range("A2").Select
> Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(5), _
> Replace:=True, PageBreaks:=True, SummaryBelowData:=True
> ActiveWindow.SelectedSheets.PrintPreview
>
> In place of the (XXXXXXXXXX), I need a command that will move down one row
> and then continue with the rest of the macro.
>

I appreciate the response, but when I tried that, the line turned yellow and
the macro wouldn't run.

"bpeltzer" wrote:

> Selection.Offset(1, 0).Select
>
> "Loris" wrote:
>
> > In Excel 2002, I need a Macro that will select a range containing data in a
> > spicific column, then move down one row belosw the rand and select all rows
> > from that point to bottom of worksheet and delete those rows. current my
> > Macro has the following lines:
> > Sheets("Supplier").Select
> > Range("A1:R220").Sort Key1:=Range("C2"), Order1:=xlAscending,
> > Key2:=Range _
> > ("D2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
> > MatchCase _
> > :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
> > DataOption2:=xlSortNormal
> > Range("C1").Select
> > Selection.End(xlDown).Select
> > (XXXXXXXXXXX)
> > Range(Selection, Selection.End(xlDown)).Select
> > Selection.Delete Shift:=xlUp
> > Range("A2").Select
> > Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(5), _
> > Replace:=True, PageBreaks:=True, SummaryBelowData:=True
> > ActiveWindow.SelectedSheets.PrintPreview
> >
> > In place of the (XXXXXXXXXX), I need a command that will move down one row
> > and then continue with the rest of the macro.
> >

Tom, how do I then get Excel to select all the rows from that point to the
bottom of the worksheet since I don't know what the starting row will be???

"Tom Ogilvy" wrote:

> selection.Offset(1,0).Select
>
> --
> Regards,
> Tom Ogilvy
>
> "Loris" <Loris@discussions.microsoft.com> wrote in message
> news:23540564-FAE6-48B8-B33E-DF27CFB8C76A@microsoft.com...
> > In Excel 2002, I need a Macro that will select a range containing data in
> a
> > spicific column, then move down one row belosw the rand and select all
> rows
> > from that point to bottom of worksheet and delete those rows. current my
> > Macro has the following lines:
> > Sheets("Supplier").Select
> > Range("A1:R220").Sort Key1:=Range("C2"), Order1:=xlAscending,
> > Key2:=Range _
> > ("D2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
> > MatchCase _
> > :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
> > DataOption2:=xlSortNormal
> > Range("C1").Select
> > Selection.End(xlDown).Select
> > (XXXXXXXXXXX)
> > Range(Selection, Selection.End(xlDown)).Select
> > Selection.Delete Shift:=xlUp
> > Range("A2").Select
> > Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(5), _
> > Replace:=True, PageBreaks:=True, SummaryBelowData:=True
> > ActiveWindow.SelectedSheets.PrintPreview
> >
> > In place of the (XXXXXXXXXX), I need a command that will move down one row
> > and then continue with the rest of the macro.
> >
>
>
>

supposedly that is what this code does:

> > Range("C1").Select
> > Selection.End(xlDown).Select
Selection.Offset(0,1).Select
> > Range(Selection, Selection.End(xlDown)).Select


Only you know what you are trying to do and how your sheet is set up.

for that last command you might want

> > Range(Selection, Selection.End(xlDown)).EntireRow.Select



--
Regards,
Tom Ogilvy

"Loris" <Loris@discussions.microsoft.com> wrote in message
news:E7996DFC-32E5-499A-B8A5-08EF5ABD14AB@microsoft.com...
> Tom, how do I then get Excel to select all the rows from that point to the
> bottom of the worksheet since I don't know what the starting row will
be???
>
> "Tom Ogilvy" wrote:
>
> > selection.Offset(1,0).Select
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Loris" <Loris@discussions.microsoft.com> wrote in message
> > news:23540564-FAE6-48B8-B33E-DF27CFB8C76A@microsoft.com...
> > > In Excel 2002, I need a Macro that will select a range containing
data in
> > a
> > > spicific column, then move down one row belosw the rand and select all
> > rows
> > > from that point to bottom of worksheet and delete those rows. current
my
> > > Macro has the following lines:
> > > Sheets("Supplier").Select
> > > Range("A1:R220").Sort Key1:=Range("C2"), Order1:=xlAscending,
> > > Key2:=Range _
> > > ("D2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
> > > MatchCase _
> > > :=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, _
> > > DataOption2:=xlSortNormal
> > > Range("C1").Select
> > > Selection.End(xlDown).Select
> > > (XXXXXXXXXXX)
> > > Range(Selection, Selection.End(xlDown)).Select
> > > Selection.Delete Shift:=xlUp
> > > Range("A2").Select
> > > Selection.Subtotal GroupBy:=3, Function:=xlSum,
TotalList:=Array(5), _
> > > Replace:=True, PageBreaks:=True, SummaryBelowData:=True
> > > ActiveWindow.SelectedSheets.PrintPreview
> > >
> > > In place of the (XXXXXXXXXX), I need a command that will move down one
row
> > > and then continue with the rest of the macro.
> > >
> >
> >
> >

The EntireRow.Select is what I needed. Thanks, Tom

"Tom Ogilvy" wrote:

> supposedly that is what this code does:
>
> > > Range("C1").Select
> > > Selection.End(xlDown).Select
> Selection.Offset(0,1).Select
> > > Range(Selection, Selection.End(xlDown)).Select
>
>
> Only you know what you are trying to do and how your sheet is set up.
>
> for that last command you might want
>
> > > Range(Selection, Selection.End(xlDown)).EntireRow.Select
>
>
>
> --
> Regards,
> Tom Ogilvy
>
> "Loris" <Loris@discussions.microsoft.com> wrote in message
> news:E7996DFC-32E5-499A-B8A5-08EF5ABD14AB@microsoft.com...
> > Tom, how do I then get Excel to select all the rows from that point to the
> > bottom of the worksheet since I don't know what the starting row will
> be???
> >
> > "Tom Ogilvy" wrote:
> >
> > > selection.Offset(1,0).Select
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Loris" <Loris@discussions.microsoft.com> wrote in message
> > > news:23540564-FAE6-48B8-B33E-DF27CFB8C76A@microsoft.com...
> > > > In Excel 2002, I need a Macro that will select a range containing
> data in
> > > a
> > > > spicific column, then move down one row belosw the rand and select all
> > > rows
> > > > from that point to bottom of worksheet and delete those rows. current
> my
> > > > Macro has the following lines:
> > > > Sheets("Supplier").Select
> > > > Range("A1:R220").Sort Key1:=Range("C2"), Order1:=xlAscending,
> > > > Key2:=Range _
> > > > ("D2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
> > > > MatchCase _
> > > > :=False, Orientation:=xlTopToBottom,
> DataOption1:=xlSortNormal, _
> > > > DataOption2:=xlSortNormal
> > > > Range("C1").Select
> > > > Selection.End(xlDown).Select
> > > > (XXXXXXXXXXX)
> > > > Range(Selection, Selection.End(xlDown)).Select
> > > > Selection.Delete Shift:=xlUp
> > > > Range("A2").Select
> > > > Selection.Subtotal GroupBy:=3, Function:=xlSum,
> TotalList:=Array(5), _
> > > > Replace:=True, PageBreaks:=True, SummaryBelowData:=True
> > > > ActiveWindow.SelectedSheets.PrintPreview
> > > >
> > > > In place of the (XXXXXXXXXX), I need a command that will move down one
> row
> > > > and then continue with the rest of the macro.
> > > >
> > >
> > >
> > >
>
>
>