Hi

I've tried many work-arounds & read many posts - it's probably
something simple so please bare with this learner.

please help with the correct syntax for these lines:
'tbl.Offset(0, 0).Resize(tbl.Rows(0), tbl.Columns(2)).Select &
'.Offset(0, -2).Select

I have CurrentRegion selected (A to I, variable rows)
I want to custom sort on K which contains a UDF which inserts the
colour index of col G (no problems with this part)
J is empty (when I tried the UDF in J the index value & sort kept
messing up - #Value)
& then
re-select the original CurrentRegion so that I can set the print
range.

Sub SortByColourWorkLog()
Dim tbl As Range
'
'sorts on =ColorIndexOfCell(G2) function in mod 1
'
Application.Volatile
Selection.CurrentRegion.Select
Set tbl = ActiveCell.CurrentRegion
'tbl.Offset(0, 0).Resize(tbl.Rows(0), tbl.Columns(2)).Select '(error
occuring here)

Selection.Sort Key1:=Range("K2"), Order1:=xlDescending,
Key2:=Range("H2") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=7,
MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:= _
xlSortNormal
'.Offset(0, -2).Select '(want to re-select the original
"CurrentRegion")
ActiveSheet.PageSetup.PrintArea = Selection.CurrentRegion
End Sub

Cheers again
Peta

an area is just like a worksheet it starts with row 1 and column 1. There is
no such thing as row(0). Arrays do start at index 0.

" wrote:

Hi

I've tried many work-arounds & read many posts - it's probably
something simple so please bare with this learner.

please help with the correct syntax for these lines:
'tbl.Offset(0, 0).Resize(tbl.Rows(0), tbl.Columns(2)).Select &
'.Offset(0, -2).Select

I have CurrentRegion selected (A to I, variable rows)
I want to custom sort on K which contains a UDF which inserts the
colour index of col G (no problems with this part)
J is empty (when I tried the UDF in J the index value & sort kept
messing up - #Value)
& then
re-select the original CurrentRegion so that I can set the print
range.

Sub SortByColourWorkLog()
Dim tbl As Range
'
'sorts on =ColorIndexOfCell(G2) function in mod 1
'
Application.Volatile
Selection.CurrentRegion.Select
Set tbl = ActiveCell.CurrentRegion
'tbl.Offset(0, 0).Resize(tbl.Rows(0), tbl.Columns(2)).Select '(error
occuring here)

Selection.Sort Key1:=Range("K2"), Order1:=xlDescending,
Key2:=Range("H2") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=7,
MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:= _
xlSortNormal
'.Offset(0, -2).Select '(want to re-select the original
"CurrentRegion")
ActiveSheet.PageSetup.PrintArea = Selection.CurrentRegion
End Sub

Cheers again
Peta

On Jun 1, 9:25*pm, Joel wrote:
an area is just like a worksheet it starts with row 1 and column 1. *There is
no such thing as row(0). *Arrays do start at index 0.



" wrote:
Hi

I've tried many work-arounds & read many posts - it's probably
something simple so please bare with this learner.

please help with the correct syntax for these lines:
'tbl.Offset(0, 0).Resize(tbl.Rows(0), tbl.Columns(2)).Select &
*'.Offset(0, -2).Select

I have CurrentRegion selected (A to I, variable rows)
I want to custom sort on K which contains a UDF which inserts the
colour index of col G (no problems with this part)
J is empty *(when I tried the UDF in J the index value & sort kept
messing up - #Value)
& then
re-select the original CurrentRegion so that I can set the print
range.

Sub SortByColourWorkLog()
Dim tbl As Range
'
'sorts on =ColorIndexOfCell(G2) function in mod 1
'
Application.Volatile
Selection.CurrentRegion.Select
Set tbl = ActiveCell.CurrentRegion
'tbl.Offset(0, 0).Resize(tbl.Rows(0), tbl.Columns(2)).Select '(error
occuring here)

* Selection.Sort Key1:=Range("K2"), Order1:=xlDescending,
Key2:=Range("H2") _
* * * * , Order2:=xlAscending, Header:=xlYes, OrderCustom:=7,
MatchCase:=False _
* * * * , Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:= _
* * * * xlSortNormal
* *'.Offset(0, -2).Select * '(want to re-select the original
"CurrentRegion")
* * ActiveSheet.PageSetup.PrintArea = Selection.CurrentRegion
End Sub

Cheers again
Peta- Hide quoted text -

- Show quoted text -

Hi Joel

thanks for you reply, sorry for the delay - I've had trouble re-
posting.

I had previously tried: tbl.Offset(0, 2).Resize(tbl.Rows(0),
tbl.Columns(2)).Select
and it moved the whole selected region two columns to the right. Hence
I though (0, 0) might keep it where it was & extend 2 columns with
tbl.Columns(2)...

Anyway I tried tbl.Offset(1, 1).Resize(tbl.Rows(0),
tbl.Columns(2)).Select
which is what I though you were getting at and got the same error
message:
1004 application or object defined error.

If you or anyone else has any further help or comments I'd be very
greatful.
regards
Peta

There are two reasons I have trouble login. first the website doesn't allow
you top lgin twice. If you have trouble login first sign-out using the
button in the top right hand -corner of the window. Sometimes when the site
is busy it doesn't allow you to login. Use BACK to retry (don't use refresh
it moves you to another page and looses the info you typed).

You can use
Set tbl = ActiveCell.CurrentRegion
tbl(1, 3).Resize(tbl.Rows.Count, tbl.Columns.Count - 2).Select

I prefer
Set tbl = ActiveCell.CurrentRegion
ActiveSheet.Range(tbl(1, 3), _
tbl(tbl.Rows.Count, tbl.Columns.Count)).Select


"NewbiePete" wrote:

On Jun 1, 9:25 pm, Joel wrote:
an area is just like a worksheet it starts with row 1 and column 1. There is
no such thing as row(0). Arrays do start at index 0.



" wrote:
Hi

I've tried many work-arounds & read many posts - it's probably
something simple so please bare with this learner.

please help with the correct syntax for these lines:
'tbl.Offset(0, 0).Resize(tbl.Rows(0), tbl.Columns(2)).Select &
'.Offset(0, -2).Select

I have CurrentRegion selected (A to I, variable rows)
I want to custom sort on K which contains a UDF which inserts the
colour index of col G (no problems with this part)
J is empty (when I tried the UDF in J the index value & sort kept
messing up - #Value)
& then
re-select the original CurrentRegion so that I can set the print
range.

Sub SortByColourWorkLog()
Dim tbl As Range
'
'sorts on =ColorIndexOfCell(G2) function in mod 1
'
Application.Volatile
Selection.CurrentRegion.Select
Set tbl = ActiveCell.CurrentRegion
'tbl.Offset(0, 0).Resize(tbl.Rows(0), tbl.Columns(2)).Select '(error
occuring here)

Selection.Sort Key1:=Range("K2"), Order1:=xlDescending,
Key2:=Range("H2") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=7,
MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:= _
xlSortNormal
'.Offset(0, -2).Select '(want to re-select the original
"CurrentRegion")
ActiveSheet.PageSetup.PrintArea = Selection.CurrentRegion
End Sub

Cheers again
Peta- Hide quoted text -

- Show quoted text -

Hi Joel

thanks for you reply, sorry for the delay - I've had trouble re-
posting.

I had previously tried: tbl.Offset(0, 2).Resize(tbl.Rows(0),
tbl.Columns(2)).Select
and it moved the whole selected region two columns to the right. Hence
I though (0, 0) might keep it where it was & extend 2 columns with
tbl.Columns(2)...

Anyway I tried tbl.Offset(1, 1).Resize(tbl.Rows(0),
tbl.Columns(2)).Select
which is what I though you were getting at and got the same error
message:
1004 application or object defined error.

If you or anyone else has any further help or comments I'd be very
greatful.
regards
Peta

On Jun 2, 1:37*am, Joel wrote:
There are two reasons I have trouble login. *first the website doesn't allow
you top lgin twice. *If you have trouble login first sign-out using the
button in the top right hand -corner of the window. *Sometimes when the site
is busy it doesn't allow you to login. *Use BACK to retry (don't use refresh
it moves you to another page and looses the info you typed).

You can *use
Set tbl = ActiveCell.CurrentRegion
tbl(1, 3).Resize(tbl.Rows.Count, tbl.Columns.Count - 2).Select

I prefer
Set tbl = ActiveCell.CurrentRegion
ActiveSheet.Range(tbl(1, 3), _
* *tbl(tbl.Rows.Count, tbl.Columns.Count)).Select



"NewbiePete" wrote:
On Jun 1, 9:25 pm, Joel wrote:
an area is just like a worksheet it starts with row 1 and column 1. *There is
no such thing as row(0). *Arrays do start at index 0.

" wrote:
Hi

I've tried many work-arounds & read many posts - it's probably
something simple so please bare with this learner.

please help with the correct syntax for these lines:
'tbl.Offset(0, 0).Resize(tbl.Rows(0), tbl.Columns(2)).Select &
*'.Offset(0, -2).Select

I have CurrentRegion selected (A to I, variable rows)
I want to custom sort on K which contains a UDF which inserts the
colour index of col G (no problems with this part)
J is empty *(when I tried the UDF in J the index value & sort kept
messing up - #Value)
& then
re-select the original CurrentRegion so that I can set the print
range.

Sub SortByColourWorkLog()
Dim tbl As Range
'
'sorts on =ColorIndexOfCell(G2) function in mod 1
'
Application.Volatile
Selection.CurrentRegion.Select
Set tbl = ActiveCell.CurrentRegion
'tbl.Offset(0, 0).Resize(tbl.Rows(0), tbl.Columns(2)).Select '(error
occuring here)

* Selection.Sort Key1:=Range("K2"), Order1:=xlDescending,
Key2:=Range("H2") _
* * * * , Order2:=xlAscending, Header:=xlYes, OrderCustom:=7,
MatchCase:=False _
* * * * , Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:= _
* * * * xlSortNormal
* *'.Offset(0, -2).Select * '(want to re-select the original
"CurrentRegion")
* * ActiveSheet.PageSetup.PrintArea = Selection.CurrentRegion
End Sub

Cheers again
Peta- Hide quoted text -

- Show quoted text -

Hi Joel

thanks for you reply, sorry for the delay - I've had trouble re-
posting.

I had previously tried: tbl.Offset(0, 2).Resize(tbl.Rows(0),
tbl.Columns(2)).Select
and it moved the whole selected region two columns to the right. Hence
I though (0, 0) might keep it where it was & extend 2 columns with
tbl.Columns(2)...

Anyway I tried tbl.Offset(1, 1).Resize(tbl.Rows(0),
tbl.Columns(2)).Select
which is what I though you were getting at and got the same error
message:
1004 application or object defined error.

If you or anyone else has any further help or comments I'd be very
greatful.
regards
Peta- Hide quoted text -

- Show quoted text -

Good morning Joel

I got the above to work by modifying your code slightly:
ActiveSheet.Range(tbl(1, 1), _
tbl(tbl.Rows.Count, tbl.Columns.Count + 2)).Select

as it was it de-selected cols A & B.

thanks

Peta