extend selected CurrentRegion results in application or objectdefined error 1004

[pgcn]

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

 

[Joel]

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.

 

[NewbiePete]

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.











- 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

 

[Joel]

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]

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










- 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