Problem with Offset, unable to get the right Syntax

[darekknox]

Hi All,

I have a trouble with the following line of the 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

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

But it gives me Object defining error.

Could you please help me?

 

[Rick Rothstein]

Re: Problem with Offset, unable to get thew right Syntax

It gives $A$1:$r$18

I want it to copy from column A to G

Assuming Column K contains constants (that is, no formulas), then if I understand your originally posted code coupled with the above statement correctly, I think this single line of code will do what all of your code was attempting to do...

[table="width: 500"]
[tr]
	[td]Intersect(Range("K5", Cells(Rows.Count, "K").End(xlUp)).SpecialCells(xlConstants).EntireRow, Columns("A:G")).Copy[/td]
[/tr]
[/table]

 

[darekknox]

Re: Problem with Offset, unable to get thew right Syntax

Thank you all for help, topic can be closed.

Issue solved with the following line:

Range(Range("a6"), Cells(Rows.Count, "g").End(xlUp)).SpecialCells(xlVisible).Copy

 

[Rick Rothstein]

Re: Problem with Offset, unable to get thew right Syntax

Thank you all for help, topic can be closed.

Issue solved with the following line:

Range(Range("a6"), Cells(Rows.Count, "g").End(xlUp)).SpecialCells(xlVisible).Copy

I am curious as to whether you tried my one-line replacement for all the code you posted in your original message and, if so, did it in fact work for you.

 

[darekknox]

Re: Problem with Offset, unable to get thew right Syntax

Hi Rick sorry for my late response. I just checked it (sorry I did not notice your post before). Code provided by you works, it copies the required range but with headers.

 

[Rick Rothstein]

Re: Problem with Offset, unable to get thew right Syntax

Hi Rick sorry for my late response. I just checked it (sorry I did not notice your post before). Code provided by you works, it copies the required range but with headers.

If Row 5 is your header row, then we just need to change the 5 in my code line to a 6...

[table="width: 500"]
[tr]
	[td]Intersect(Range("K[B][COLOR="#0000FF"]6[/COLOR][/B]", Cells(Rows.Count, "K").End(xlUp)).SpecialCells(xlConstants).EntireRow, Columns("A:G")).Copy[/td]
[/tr]
[/table]