Table to sort via VBA but row&column size variable!

[J_J]

Hi,
I have a table in the region "B5:F130" where the cells B5, C5, D5, E5, F5
represents the Data Labels and the rest downwords the data. I want to sort
this table with referece to the numeric data in column B (which are in
region "B6:B130") with VBA coding. The problem is that, although the
starting cell of this table is stable, but the number of rows and columns of
this table may change. So the table may reside in B5:H140 or B5: J150....etc
in the future. Thus I need to use an expression in the code so that sorting
of this table can be done whatever the row & column size is (of course not
exceeding excel limitations).
Can anyone suggest a code that will do that please?
Thank you in advance
J_J

[Bob Phillips]

cLastRow = Cells(Rows.Count,"B").End(xlUp).Row
cLastCol = Cells(5,Columns.Count).End(xlToLeft).Column
Set rng = Range("B5",Cells(cLastRow,cLastCol)
rng.Sort etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"J_J" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> I have a table in the region "B5:F130" where the cells B5, C5, D5, E5, F5
> represents the Data Labels and the rest downwords the data. I want to sort
> this table with referece to the numeric data in column B (which are in
> region "B6:B130") with VBA coding. The problem is that, although the
> starting cell of this table is stable, but the number of rows and columns
of
> this table may change. So the table may reside in B5:H140 or B5:
J150....etc
> in the future. Thus I need to use an expression in the code so that
sorting
> of this table can be done whatever the row & column size is (of course not
> exceeding excel limitations).
> Can anyone suggest a code that will do that please?
> Thank you in advance
> J_J
>
>

[J_J]

Thank you Bob,
I wish I could finish the rest of the code...
J_J

"Bob Phillips" <[email protected]> wrote in message
news:%23%[email protected]...
>
> cLastRow = Cells(Rows.Count,"B").End(xlUp).Row
> cLastCol = Cells(5,Columns.Count).End(xlToLeft).Column
> Set rng = Range("B5",Cells(cLastRow,cLastCol)
> rng.Sort etc.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "J_J" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>> I have a table in the region "B5:F130" where the cells B5, C5, D5, E5, F5
>> represents the Data Labels and the rest downwords the data. I want to
>> sort
>> this table with referece to the numeric data in column B (which are in
>> region "B6:B130") with VBA coding. The problem is that, although the
>> starting cell of this table is stable, but the number of rows and columns
> of
>> this table may change. So the table may reside in B5:H140 or B5:
> J150....etc
>> in the future. Thus I need to use an expression in the code so that
> sorting
>> of this table can be done whatever the row & column size is (of course
>> not
>> exceeding excel limitations).
>> Can anyone suggest a code that will do that please?
>> Thank you in advance
>> J_J
>>
>>
>
>

[Bob Phillips]

I hope that you caught the missing ) in

Set rng = Range("B5",Cells(cLastRow,cLastCol)

which should be

Set rng = Range("B5",Cells(cLastRow,cLastCol))

--
HTH

Bob Phillips

"J_J" <[email protected]> wrote in message
news:[email protected]...
> Thank you Bob,
> I wish I could finish the rest of the code...
> J_J
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%23%[email protected]...
> >
> > cLastRow = Cells(Rows.Count,"B").End(xlUp).Row
> > cLastCol = Cells(5,Columns.Count).End(xlToLeft).Column
> > Set rng = Range("B5",Cells(cLastRow,cLastCol)
> > rng.Sort etc.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "J_J" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi,
> >> I have a table in the region "B5:F130" where the cells B5, C5, D5, E5,
F5
> >> represents the Data Labels and the rest downwords the data. I want to
> >> sort
> >> this table with referece to the numeric data in column B (which are in
> >> region "B6:B130") with VBA coding. The problem is that, although the
> >> starting cell of this table is stable, but the number of rows and
columns
> > of
> >> this table may change. So the table may reside in B5:H140 or B5:
> > J150....etc
> >> in the future. Thus I need to use an expression in the code so that
> > sorting
> >> of this table can be done whatever the row & column size is (of course
> >> not
> >> exceeding excel limitations).
> >> Can anyone suggest a code that will do that please?
> >> Thank you in advance
> >> J_J
> >>
> >>
> >
> >
>
>

[EvolBob]

You shouldn't need to find this out, as XL usually finds the range as the
currentregion, to sort with automatically. If you don't have blank rows,
use this.

[A5].Sort key1:=[B5], order1:=xlAscending, header:=xlYes

Sorting is a good way of clearing out the blanks so it doesn't mater if you
include a whole lot more rows than you will ever have - just to make sure.
So this change shouldn't make any difference in speed than using the exact
range.

Range("A5", Cells(6000, [b5].CurrentRegion.Columns.Count)).Sort _
key1:=[b5], order1:=xlAscending, header:=xlYes

Works out the columns, and catches any blank rows in the table.
Increase the 6000 row number if you think the data would exceed this row
limit.

Both the code snippets work if you don't have empty column headers.


Regards
Robert McCurdy

"J_J" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> I have a table in the region "B5:F130" where the cells B5, C5, D5, E5, F5
> represents the Data Labels and the rest downwords the data. I want to sort
> this table with referece to the numeric data in column B (which are in
> region "B6:B130") with VBA coding. The problem is that, although the
> starting cell of this table is stable, but the number of rows and columns
> of this table may change. So the table may reside in B5:H140 or B5:
> J150....etc in the future. Thus I need to use an expression in the code so
> that sorting of this table can be done whatever the row & column size is
> (of course not exceeding excel limitations).
> Can anyone suggest a code that will do that please?
> Thank you in advance
> J_J
>
>

[Tom Ogilvy]

Sub SortDataOnActiveSheet()
Dim cLastRow as Long, cLastCol as Long
Dim rng as Range
cLastRow = Cells(Rows.Count,"B").End(xlUp).Row
cLastCol = Cells(5,Columns.Count).End(xlToLeft).Column
Set rng = Range("B5",Cells(cLastRow,cLastCol))
rng..Sort Key1:=Range("B6"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub

would be about all you need to finish it.

--
Regards,
Tom Ogilvy


"J_J" <[email protected]> wrote in message
news:[email protected]...
> Thank you Bob,
> I wish I could finish the rest of the code...
> J_J
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%23%[email protected]...
> >
> > cLastRow = Cells(Rows.Count,"B").End(xlUp).Row
> > cLastCol = Cells(5,Columns.Count).End(xlToLeft).Column
> > Set rng = Range("B5",Cells(cLastRow,cLastCol)
> > rng.Sort etc.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "J_J" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi,
> >> I have a table in the region "B5:F130" where the cells B5, C5, D5, E5,
F5
> >> represents the Data Labels and the rest downwords the data. I want to
> >> sort
> >> this table with referece to the numeric data in column B (which are in
> >> region "B6:B130") with VBA coding. The problem is that, although the
> >> starting cell of this table is stable, but the number of rows and
columns
> > of
> >> this table may change. So the table may reside in B5:H140 or B5:
> > J150....etc
> >> in the future. Thus I need to use an expression in the code so that
> > sorting
> >> of this table can be done whatever the row & column size is (of course
> >> not
> >> exceeding excel limitations).
> >> Can anyone suggest a code that will do that please?
> >> Thank you in advance
> >> J_J
> >>
> >>
> >
> >
>
>

[J_J]

Hi Bob,

Yes...it worked like a charm. Here is the code I call:
'-------------------
Sub Sort_it()
' Bobs
Dim rng As Range
With ActiveSheet
cLastRow = Cells(Rows.Count, "B").End(xlUp).Row
cLastCol = Cells(5, Columns.Count).End(xlToLeft).Column
Set rng = Range("B5", Cells(cLastRow, cLastCol))
rng.Sort Key1:=Range("C6"), Order1:=xlDescending
End With
End Sub
'---------------
Regards
J_J



"Bob Phillips" <[email protected]> wrote in message
news:e%[email protected]...
>I hope that you caught the missing ) in
>
> Set rng = Range("B5",Cells(cLastRow,cLastCol)
>
> which should be
>
> Set rng = Range("B5",Cells(cLastRow,cLastCol))
>
> --
> HTH
>
> Bob Phillips
>
> "J_J" <[email protected]> wrote in message
> news:[email protected]...
>> Thank you Bob,
>> I wish I could finish the rest of the code...
>> J_J
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:%23%[email protected]...
>> >
>> > cLastRow = Cells(Rows.Count,"B").End(xlUp).Row
>> > cLastCol = Cells(5,Columns.Count).End(xlToLeft).Column
>> > Set rng = Range("B5",Cells(cLastRow,cLastCol)
>> > rng.Sort etc.
>> >
>> > --
>> >
>> > HTH
>> >
>> > RP
>> > (remove nothere from the email address if mailing direct)
>> >
>> >
>> > "J_J" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi,
>> >> I have a table in the region "B5:F130" where the cells B5, C5, D5, E5,
> F5
>> >> represents the Data Labels and the rest downwords the data. I want to
>> >> sort
>> >> this table with referece to the numeric data in column B (which are in
>> >> region "B6:B130") with VBA coding. The problem is that, although the
>> >> starting cell of this table is stable, but the number of rows and
> columns
>> > of
>> >> this table may change. So the table may reside in B5:H140 or B5:
>> > J150....etc
>> >> in the future. Thus I need to use an expression in the code so that
>> > sorting
>> >> of this table can be done whatever the row & column size is (of course
>> >> not
>> >> exceeding excel limitations).
>> >> Can anyone suggest a code that will do that please?
>> >> Thank you in advance
>> >> J_J
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[J_J]

Thank you for the explanations EvolBob,
Bob's code solved my problem.
J_J

"EvolBob" <[email protected]> wrote in message
news:uYc%[email protected]...
> You shouldn't need to find this out, as XL usually finds the range as the
> currentregion, to sort with automatically. If you don't have blank rows,
> use this.
>
> [A5].Sort key1:=[B5], order1:=xlAscending, header:=xlYes
>
> Sorting is a good way of clearing out the blanks so it doesn't mater if
> you include a whole lot more rows than you will ever have - just to make
> sure.
> So this change shouldn't make any difference in speed than using the exact
> range.
>
> Range("A5", Cells(6000, [b5].CurrentRegion.Columns.Count)).Sort _
> key1:=[b5], order1:=xlAscending, header:=xlYes
>
> Works out the columns, and catches any blank rows in the table.
> Increase the 6000 row number if you think the data would exceed this row
> limit.
>
> Both the code snippets work if you don't have empty column headers.
>
>
> Regards
> Robert McCurdy
>
> "J_J" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>> I have a table in the region "B5:F130" where the cells B5, C5, D5, E5, F5
>> represents the Data Labels and the rest downwords the data. I want to
>> sort this table with referece to the numeric data in column B (which are
>> in region "B6:B130") with VBA coding. The problem is that, although the
>> starting cell of this table is stable, but the number of rows and columns
>> of this table may change. So the table may reside in B5:H140 or B5:
>> J150....etc in the future. Thus I need to use an expression in the code
>> so that sorting of this table can be done whatever the row & column size
>> is (of course not exceeding excel limitations).
>> Can anyone suggest a code that will do that please?
>> Thank you in advance
>> J_J
>>
>>
>

[J_J]

Thank you Tom,

With great support from the NG, that part is OK now.
I have a simple follow-up Q. on the same code:

Range("A6:A430").Value = "X"

I need to change this code into something like this:

For q=6 to 430
If B(q)<>"" then
A(q).Value="X"
Next

I am trying to set a loop to mark cells (A6:A430) with "X" for thouse only
having a non empty cell nearby (B6:B430).

J_J


"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> Sub SortDataOnActiveSheet()
> Dim cLastRow as Long, cLastCol as Long
> Dim rng as Range
> cLastRow = Cells(Rows.Count,"B").End(xlUp).Row
> cLastCol = Cells(5,Columns.Count).End(xlToLeft).Column
> Set rng = Range("B5",Cells(cLastRow,cLastCol))
> rng..Sort Key1:=Range("B6"), Order1:=xlAscending, _
> Header:=xlYes, OrderCustom:=1, _
> MatchCase:=False, Orientation:=xlTopToBottom
> End Sub
>
> would be about all you need to finish it.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "J_J" <[email protected]> wrote in message
> news:[email protected]...
>> Thank you Bob,
>> I wish I could finish the rest of the code...
>> J_J
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:%23%[email protected]...
>> >
>> > cLastRow = Cells(Rows.Count,"B").End(xlUp).Row
>> > cLastCol = Cells(5,Columns.Count).End(xlToLeft).Column
>> > Set rng = Range("B5",Cells(cLastRow,cLastCol)
>> > rng.Sort etc.
>> >
>> > --
>> >
>> > HTH
>> >
>> > RP
>> > (remove nothere from the email address if mailing direct)
>> >
>> >
>> > "J_J" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi,
>> >> I have a table in the region "B5:F130" where the cells B5, C5, D5, E5,
> F5
>> >> represents the Data Labels and the rest downwords the data. I want to
>> >> sort
>> >> this table with referece to the numeric data in column B (which are in
>> >> region "B6:B130") with VBA coding. The problem is that, although the
>> >> starting cell of this table is stable, but the number of rows and
> columns
>> > of
>> >> this table may change. So the table may reside in B5:H140 or B5:
>> > J150....etc
>> >> in the future. Thus I need to use an expression in the code so that
>> > sorting
>> >> of this table can be done whatever the row & column size is (of course
>> >> not
>> >> exceeding excel limitations).
>> >> Can anyone suggest a code that will do that please?
>> >> Thank you in advance
>> >> J_J
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Tom Ogilvy]

Dim q as Long
For q=6 to 430
If Cells(q,"B").Text <>"" then
Cells(q,"A").Value="X"
Next

or using numbers specify the columns

Dim q as Long
For q=6 to 430
If Cells(q,2).Text <>"" then
Cells(q,1).Value="X"
Next

--
Regards,
Tom Ogilvy


"J_J" <[email protected]> wrote in message
news:unu7%[email protected]...
> Thank you Tom,
>
> With great support from the NG, that part is OK now.
> I have a simple follow-up Q. on the same code:
>
> Range("A6:A430").Value = "X"
>
> I need to change this code into something like this:
>
> For q=6 to 430
> If B(q)<>"" then
> A(q).Value="X"
> Next
>
> I am trying to set a loop to mark cells (A6:A430) with "X" for thouse only
> having a non empty cell nearby (B6:B430).
>
> J_J
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:%[email protected]...
> > Sub SortDataOnActiveSheet()
> > Dim cLastRow as Long, cLastCol as Long
> > Dim rng as Range
> > cLastRow = Cells(Rows.Count,"B").End(xlUp).Row
> > cLastCol = Cells(5,Columns.Count).End(xlToLeft).Column
> > Set rng = Range("B5",Cells(cLastRow,cLastCol))
> > rng..Sort Key1:=Range("B6"), Order1:=xlAscending, _
> > Header:=xlYes, OrderCustom:=1, _
> > MatchCase:=False, Orientation:=xlTopToBottom
> > End Sub
> >
> > would be about all you need to finish it.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "J_J" <[email protected]> wrote in message
> > news:[email protected]...
> >> Thank you Bob,
> >> I wish I could finish the rest of the code...
> >> J_J
> >>
> >> "Bob Phillips" <[email protected]> wrote in message
> >> news:%23%[email protected]...
> >> >
> >> > cLastRow = Cells(Rows.Count,"B").End(xlUp).Row
> >> > cLastCol = Cells(5,Columns.Count).End(xlToLeft).Column
> >> > Set rng = Range("B5",Cells(cLastRow,cLastCol)
> >> > rng.Sort etc.
> >> >
> >> > --
> >> >
> >> > HTH
> >> >
> >> > RP
> >> > (remove nothere from the email address if mailing direct)
> >> >
> >> >
> >> > "J_J" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> Hi,
> >> >> I have a table in the region "B5:F130" where the cells B5, C5, D5,
E5,
> > F5
> >> >> represents the Data Labels and the rest downwords the data. I want
to
> >> >> sort
> >> >> this table with referece to the numeric data in column B (which are
in
> >> >> region "B6:B130") with VBA coding. The problem is that, although the
> >> >> starting cell of this table is stable, but the number of rows and
> > columns
> >> > of
> >> >> this table may change. So the table may reside in B5:H140 or B5:
> >> > J150....etc
> >> >> in the future. Thus I need to use an expression in the code so that
> >> > sorting
> >> >> of this table can be done whatever the row & column size is (of
course
> >> >> not
> >> >> exceeding excel limitations).
> >> >> Can anyone suggest a code that will do that please?
> >> >> Thank you in advance
> >> >> J_J
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

[J_J]

Tom,
For the loop you proposed I received a
"Next without For error" from the compiler.
J_J

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Dim q as Long
> For q=6 to 430
> If Cells(q,"B").Text <>"" then
> Cells(q,"A").Value="X"
> Next
>
> or using numbers specify the columns
>
> Dim q as Long
> For q=6 to 430
> If Cells(q,2).Text <>"" then
> Cells(q,1).Value="X"
> Next
>
> --
> Regards,
> Tom Ogilvy
>
>
> "J_J" <[email protected]> wrote in message
> news:unu7%[email protected]...
>> Thank you Tom,
>>
>> With great support from the NG, that part is OK now.
>> I have a simple follow-up Q. on the same code:
>>
>> Range("A6:A430").Value = "X"
>>
>> I need to change this code into something like this:
>>
>> For q=6 to 430
>> If B(q)<>"" then
>> A(q).Value="X"
>> Next
>>
>> I am trying to set a loop to mark cells (A6:A430) with "X" for thouse
>> only
>> having a non empty cell nearby (B6:B430).
>>
>> J_J
>>
>>
>> "Tom Ogilvy" <[email protected]> wrote in message
>> news:%[email protected]...
>> > Sub SortDataOnActiveSheet()
>> > Dim cLastRow as Long, cLastCol as Long
>> > Dim rng as Range
>> > cLastRow = Cells(Rows.Count,"B").End(xlUp).Row
>> > cLastCol = Cells(5,Columns.Count).End(xlToLeft).Column
>> > Set rng = Range("B5",Cells(cLastRow,cLastCol))
>> > rng..Sort Key1:=Range("B6"), Order1:=xlAscending, _
>> > Header:=xlYes, OrderCustom:=1, _
>> > MatchCase:=False, Orientation:=xlTopToBottom
>> > End Sub
>> >
>> > would be about all you need to finish it.
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> > "J_J" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Thank you Bob,
>> >> I wish I could finish the rest of the code...
>> >> J_J
>> >>
>> >> "Bob Phillips" <[email protected]> wrote in message
>> >> news:%23%[email protected]...
>> >> >
>> >> > cLastRow = Cells(Rows.Count,"B").End(xlUp).Row
>> >> > cLastCol = Cells(5,Columns.Count).End(xlToLeft).Column
>> >> > Set rng = Range("B5",Cells(cLastRow,cLastCol)
>> >> > rng.Sort etc.
>> >> >
>> >> > --
>> >> >
>> >> > HTH
>> >> >
>> >> > RP
>> >> > (remove nothere from the email address if mailing direct)
>> >> >
>> >> >
>> >> > "J_J" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> Hi,
>> >> >> I have a table in the region "B5:F130" where the cells B5, C5, D5,
> E5,
>> > F5
>> >> >> represents the Data Labels and the rest downwords the data. I want
> to
>> >> >> sort
>> >> >> this table with referece to the numeric data in column B (which are
> in
>> >> >> region "B6:B130") with VBA coding. The problem is that, although
>> >> >> the
>> >> >> starting cell of this table is stable, but the number of rows and
>> > columns
>> >> > of
>> >> >> this table may change. So the table may reside in B5:H140 or B5:
>> >> > J150....etc
>> >> >> in the future. Thus I need to use an expression in the code so that
>> >> > sorting
>> >> >> of this table can be done whatever the row & column size is (of
> course
>> >> >> not
>> >> >> exceeding excel limitations).
>> >> >> Can anyone suggest a code that will do that please?
>> >> >> Thank you in advance
>> >> >> J_J
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Tom Ogilvy]

Omitted the Underscore to make it a single command IF statement. Sorry.

Dim q as Long
For q=6 to 430
If Cells(q,"B").Text <>"" then _
Cells(q,"A").Value="X"
Next

or using numbers specify the columns

Dim q as Long
For q=6 to 430
If Cells(q,2).Text <>"" then _
Cells(q,1).Value="X"
Next

--
Regards,
Tom Ogilvy


"J_J" <[email protected]> wrote in message
news:[email protected]...
> Tom,
> For the loop you proposed I received a
> "Next without For error" from the compiler.
> J_J
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > Dim q as Long
> > For q=6 to 430
> > If Cells(q,"B").Text <>"" then
> > Cells(q,"A").Value="X"
> > Next
> >
> > or using numbers specify the columns
> >
> > Dim q as Long
> > For q=6 to 430
> > If Cells(q,2).Text <>"" then
> > Cells(q,1).Value="X"
> > Next
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "J_J" <[email protected]> wrote in message
> > news:unu7%[email protected]...
> >> Thank you Tom,
> >>
> >> With great support from the NG, that part is OK now.
> >> I have a simple follow-up Q. on the same code:
> >>
> >> Range("A6:A430").Value = "X"
> >>
> >> I need to change this code into something like this:
> >>
> >> For q=6 to 430
> >> If B(q)<>"" then
> >> A(q).Value="X"
> >> Next
> >>
> >> I am trying to set a loop to mark cells (A6:A430) with "X" for thouse
> >> only
> >> having a non empty cell nearby (B6:B430).
> >>
> >> J_J
> >>
> >>
> >> "Tom Ogilvy" <[email protected]> wrote in message
> >> news:%[email protected]...
> >> > Sub SortDataOnActiveSheet()
> >> > Dim cLastRow as Long, cLastCol as Long
> >> > Dim rng as Range
> >> > cLastRow = Cells(Rows.Count,"B").End(xlUp).Row
> >> > cLastCol = Cells(5,Columns.Count).End(xlToLeft).Column
> >> > Set rng = Range("B5",Cells(cLastRow,cLastCol))
> >> > rng..Sort Key1:=Range("B6"), Order1:=xlAscending, _
> >> > Header:=xlYes, OrderCustom:=1, _
> >> > MatchCase:=False, Orientation:=xlTopToBottom
> >> > End Sub
> >> >
> >> > would be about all you need to finish it.
> >> >
> >> > --
> >> > Regards,
> >> > Tom Ogilvy
> >> >
> >> >
> >> > "J_J" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> Thank you Bob,
> >> >> I wish I could finish the rest of the code...
> >> >> J_J
> >> >>
> >> >> "Bob Phillips" <[email protected]> wrote in message
> >> >> news:%23%[email protected]...
> >> >> >
> >> >> > cLastRow = Cells(Rows.Count,"B").End(xlUp).Row
> >> >> > cLastCol = Cells(5,Columns.Count).End(xlToLeft).Column
> >> >> > Set rng = Range("B5",Cells(cLastRow,cLastCol)
> >> >> > rng.Sort etc.
> >> >> >
> >> >> > --
> >> >> >
> >> >> > HTH
> >> >> >
> >> >> > RP
> >> >> > (remove nothere from the email address if mailing direct)
> >> >> >
> >> >> >
> >> >> > "J_J" <[email protected]> wrote in message
> >> >> > news:[email protected]...
> >> >> >> Hi,
> >> >> >> I have a table in the region "B5:F130" where the cells B5, C5,
D5,
> > E5,
> >> > F5
> >> >> >> represents the Data Labels and the rest downwords the data. I
want
> > to
> >> >> >> sort
> >> >> >> this table with referece to the numeric data in column B (which
are
> > in
> >> >> >> region "B6:B130") with VBA coding. The problem is that, although
> >> >> >> the
> >> >> >> starting cell of this table is stable, but the number of rows and
> >> > columns
> >> >> > of
> >> >> >> this table may change. So the table may reside in B5:H140 or B5:
> >> >> > J150....etc
> >> >> >> in the future. Thus I need to use an expression in the code so
that
> >> >> > sorting
> >> >> >> of this table can be done whatever the row & column size is (of
> > course
> >> >> >> not
> >> >> >> exceeding excel limitations).
> >> >> >> Can anyone suggest a code that will do that please?
> >> >> >> Thank you in advance
> >> >> >> J_J
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

[J_J]

Tom, thank you.
Yes that solved the problem.
J_J

"Tom Ogilvy" <[email protected]> wrote in message
news:%23%[email protected]...
> Omitted the Underscore to make it a single command IF statement. Sorry.
>
> Dim q as Long
> For q=6 to 430
> If Cells(q,"B").Text <>"" then _
> Cells(q,"A").Value="X"
> Next
>
> or using numbers specify the columns
>
> Dim q as Long
> For q=6 to 430
> If Cells(q,2).Text <>"" then _
> Cells(q,1).Value="X"
> Next
>
> --
> Regards,
> Tom Ogilvy
>
>
> "J_J" <[email protected]> wrote in message
> news:[email protected]...
>> Tom,
>> For the loop you proposed I received a
>> "Next without For error" from the compiler.
>> J_J
>>
>> "Tom Ogilvy" <[email protected]> wrote in message
>> news:[email protected]...
>> > Dim q as Long
>> > For q=6 to 430
>> > If Cells(q,"B").Text <>"" then
>> > Cells(q,"A").Value="X"
>> > Next
>> >
>> > or using numbers specify the columns
>> >
>> > Dim q as Long
>> > For q=6 to 430
>> > If Cells(q,2).Text <>"" then
>> > Cells(q,1).Value="X"
>> > Next
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> > "J_J" <[email protected]> wrote in message
>> > news:unu7%[email protected]...
>> >> Thank you Tom,
>> >>
>> >> With great support from the NG, that part is OK now.
>> >> I have a simple follow-up Q. on the same code:
>> >>
>> >> Range("A6:A430").Value = "X"
>> >>
>> >> I need to change this code into something like this:
>> >>
>> >> For q=6 to 430
>> >> If B(q)<>"" then
>> >> A(q).Value="X"
>> >> Next
>> >>
>> >> I am trying to set a loop to mark cells (A6:A430) with "X" for thouse
>> >> only
>> >> having a non empty cell nearby (B6:B430).
>> >>
>> >> J_J
>> >>
>> >>
>> >> "Tom Ogilvy" <[email protected]> wrote in message
>> >> news:%[email protected]...
>> >> > Sub SortDataOnActiveSheet()
>> >> > Dim cLastRow as Long, cLastCol as Long
>> >> > Dim rng as Range
>> >> > cLastRow = Cells(Rows.Count,"B").End(xlUp).Row
>> >> > cLastCol = Cells(5,Columns.Count).End(xlToLeft).Column
>> >> > Set rng = Range("B5",Cells(cLastRow,cLastCol))
>> >> > rng..Sort Key1:=Range("B6"), Order1:=xlAscending, _
>> >> > Header:=xlYes, OrderCustom:=1, _
>> >> > MatchCase:=False, Orientation:=xlTopToBottom
>> >> > End Sub
>> >> >
>> >> > would be about all you need to finish it.
>> >> >
>> >> > --
>> >> > Regards,
>> >> > Tom Ogilvy
>> >> >
>> >> >
>> >> > "J_J" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> Thank you Bob,
>> >> >> I wish I could finish the rest of the code...
>> >> >> J_J
>> >> >>
>> >> >> "Bob Phillips" <[email protected]> wrote in message
>> >> >> news:%23%[email protected]...
>> >> >> >
>> >> >> > cLastRow = Cells(Rows.Count,"B").End(xlUp).Row
>> >> >> > cLastCol = Cells(5,Columns.Count).End(xlToLeft).Column
>> >> >> > Set rng = Range("B5",Cells(cLastRow,cLastCol)
>> >> >> > rng.Sort etc.
>> >> >> >
>> >> >> > --
>> >> >> >
>> >> >> > HTH
>> >> >> >
>> >> >> > RP
>> >> >> > (remove nothere from the email address if mailing direct)
>> >> >> >
>> >> >> >
>> >> >> > "J_J" <[email protected]> wrote in message
>> >> >> > news:[email protected]...
>> >> >> >> Hi,
>> >> >> >> I have a table in the region "B5:F130" where the cells B5, C5,
> D5,
>> > E5,
>> >> > F5
>> >> >> >> represents the Data Labels and the rest downwords the data. I
> want
>> > to
>> >> >> >> sort
>> >> >> >> this table with referece to the numeric data in column B (which
> are
>> > in
>> >> >> >> region "B6:B130") with VBA coding. The problem is that, although
>> >> >> >> the
>> >> >> >> starting cell of this table is stable, but the number of rows
>> >> >> >> and
>> >> > columns
>> >> >> > of
>> >> >> >> this table may change. So the table may reside in B5:H140 or B5:
>> >> >> > J150....etc
>> >> >> >> in the future. Thus I need to use an expression in the code so
> that
>> >> >> > sorting
>> >> >> >> of this table can be done whatever the row & column size is (of
>> > course
>> >> >> >> not
>> >> >> >> exceeding excel limitations).
>> >> >> >> Can anyone suggest a code that will do that please?
>> >> >> >> Thank you in advance
>> >> >> >> J_J
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>