Problem with Offset, unable to get the right Syntax

darekknox

New Member
Joined
Feb 19, 2015
Messages
31
Hi All,

I have a trouble with the following line of the code:

Code:
 Sheets("Tab2").Select
    Rows("5:5").Select
    range("K5").Activate
    Selection.AutoFilter
    ActiveSheet.range("$A$5:$m$5").AutoFilter Field:=11, Criteria1:="<>"


    ActiveSheet.UsedRange.Offset(5, 0).SpecialCells _
       (xlCellTypeVisible).Copy

This works great, it copies data without header but I would also like it to copy 6 colums less.
I have tried

Code:
   ActiveSheet.UsedRange.Offset(5, -6).SpecialCells _
       (xlCellTypeVisible).Copy

But it gives me Object defining error.

Could you please help me?
 
Last edited:

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Re: Problem with Offset, unable to get thew right Syntax

Sounds to me like maybe your first column isn't far enough to move it six columns to the left. Your data couldn't start in anything before column H, or else you cannot move it six columns to the left.

Try this line of code and see what it returns:
Code:
MsgBox ActiveSheet.UsedRange.Address

Perhaps you are looking for RESIZE instead of OFFSET. OFFSET shifts the entire range, where RESIZE just changes/moves the ending columns/rows, but not the beginning.
See: Excel VBA Programming - The Resize Property
 
Upvote 0
Re: Problem with Offset, unable to get thew right Syntax

thank you for reply.

It gives $A$1:$r$18

I want it to copy from column A to G
 
Upvote 0
Re: Problem with Offset, unable to get thew right Syntax

So it sounds like you want to move it 6 columns to the RIGHT, not to the LEFT. So that would be a positive 6, not -6.
 
Upvote 0
Re: Problem with Offset, unable to get thew right Syntax

Positive 6 copies values starting from column G ;/ I need to copy column range from A to G. Values are filtered that's why there is an offset of 5 to copy without headers.
 
Last edited:
Upvote 0
Re: Problem with Offset, unable to get thew right Syntax

If you want to move the data, you might have to use OFFSET in your paste step.
You first select the data you want to copy (and use the Copy command or the Cut command if you actually want to move the data).
You then pick the first cell that you want to paste the data into to (where your offset command comes into play).
Then you paste it.

Sometimes you end up using OFFSET and RESIZE together. For example, if you want to exclude the first five rows, you use OFFSET to move down 5 rows, but then RESIZE, to shorten the range by 5 rows, i.e.

So listed below is an example of how you can exclude the first 5 rows of the UsedRange in a range selection:
Code:
    Dim myrange As Range
    Set myrange = ActiveSheet.UsedRange.Offset(5, 0)
    myrange.Resize(myrange.Rows.Count - 5, myrange.Columns.Count).Select
 
Last edited:
Upvote 0
Re: Problem with Offset, unable to get thew right Syntax

Thanks but still no luck. I tried to add "-6" to the code but it did not help

This is how it looks now

Code:
Sheets("Tab2").Select
    Rows("5:5").Select
    range("K5").Activate
    Selection.AutoFilter
    ActiveSheet.range("$A$5:$m$5").AutoFilter Field:=11, Criteria1:="<>"

    Dim myrange As range
    Set myrange = ActiveSheet.UsedRange.Offset(5, 0)
    myrange.Resize(myrange.Rows.Count - 5, myrange.Columns.Count - 6).Copy
    
  
    Sheets("final").Activate
    range("b2").PasteSpecial
 
Upvote 0
Re: Problem with Offset, unable to get thew right Syntax

OK. Why we take a step back here, and make sure we are all on the same page.
Why don't you just describe your page setup (where all your data resides), and tell what exactly what you want to copy and where.
Be sure to include how many columns there are initially, and how many you wish to copy.
 
Upvote 0
Re: Problem with Offset, unable to get thew right Syntax

I also think that part of the problem may actually be with your Filter.
I assume that row 5 is your header row.
Is there anything in the top 4 rows?
If so, which of these rows have entries in them?

These are the kind of things where seeing the data is a big help.
There are tools you can use to post screen images. See section B here: http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html
 
Last edited:
Upvote 0
Re: Problem with Offset, unable to get thew right Syntax

When you are setting MyRange you are establishing the beginning point of the range. In your example you are establishing the beginning point 5 rows below the start point of the UsedRange. I have a range that has a beginning point of Row 19 and Column 2. Creating a range using the Offset Range.ThisIsMyRangeOrUsedRange.Ofsset(5,0) is saying establish a range beginning at row 24, column 1. So if the used range begins at Row 1 and it is 5 rows long, then your MyRange is established at row 5. It has an address of "A5." Then in your next statement you are trying to address the beginning point minus 5 rows.
I was interrupted here for a meeting after starting this reply. It may be that this reply is outdated.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top