Hi,

I have a range object which covers multiple columns and multiple rows.  The 
first row of the range object contains Header information so its just 
basically text, but I want to be able to copy the data underneath the 
headers.  My Range object is called NewGLData and I want to copy everything 
in the second column excluding the first row?  How can I do this? 
I tried using something like this:
NewGLData(Columns(2)).Select
Selection.Copy 

But I kept receiving a Type Mismatch error, if you have any advice as to how 
I can overcome this, that would be great.

Thanks.
Simon

hi
Dim lr As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row
'i assumed that column B was your second column. change if uneven columns
Range("myrange").Offset(1, 1).Resize(lr - 1, 1).Select

you could use......
Range("myrange").Offset(1, 0).Columns(2).Select

without the dim and without finding the last row but this would copy a blank 
cell under the target column as well as the target column.

regards
FSt1


"Simon" wrote:

> Hi,
> 
> I have a range object which covers multiple columns and multiple rows.  The 
> first row of the range object contains Header information so its just 
> basically text, but I want to be able to copy the data underneath the 
> headers.  My Range object is called NewGLData and I want to copy everything 
> in the second column excluding the first row?  How can I do this? 
> I tried using something like this:
> NewGLData(Columns(2)).Select
> Selection.Copy 
> 
> But I kept receiving a Type Mismatch error, if you have any advice as to how 
> I can overcome this, that would be great.
> 
> Thanks.
> Simon

Hi Simon,

Try the following example.  Note that a space an underscore at the end of a 
line is a line break in an otherwise single line of code. Explanation of what 
the code is doing at bottom o this post.

Sub test()

Dim NewGLDData As Range

With Sheets("Sheet1")
  Set NewGLDData = .Range("C1:K30")
End With

With NewGLDData
  .Columns(2).Offset(1, 0) _
    .Resize(.Rows.Count - 1, 1).Copy _
    Destination:= _
    Sheets("Sheet2").Range("A2")
End With

End Sub

The following is actually one line of code to copy and paste without 
selecting.
..Columns(2).Offset(1, 0) _
    .Resize(.Rows.Count - 1, 1).Copy _
    Destination:= _
    Sheets("Sheet2").Range("A2")

..Columns(2) is the second column of range NewGLDData.

..Offset(1, 0) shifts range down one row off headers but that then includes 
an extra row at the bottom.

..Resize(.Rows.Count - 1, 1) reduces total rows by 1 to remove extra row at 
bottom

Remainder of Copy Destination should be self explanatory.

-- 
Regards,

OssieMac


"Simon" wrote:

> Hi,
> 
> I have a range object which covers multiple columns and multiple rows.  The 
> first row of the range object contains Header information so its just 
> basically text, but I want to be able to copy the data underneath the 
> headers.  My Range object is called NewGLData and I want to copy everything 
> in the second column excluding the first row?  How can I do this? 
> I tried using something like this:
> NewGLData(Columns(2)).Select
> Selection.Copy 
> 
> But I kept receiving a Type Mismatch error, if you have any advice as to how 
> I can overcome this, that would be great.
> 
> Thanks.
> Simon

Hi FSt1

Does it make a difference that my Range isn't a named range? the User 
selects the range from an application input box which I've stored as a Range 
object in my code, so rather than saying Range("myrange"), I could just use 
Range(myrange) correct?

Thanks.
Simon

"FSt1" wrote:

> hi
> Dim lr As Long
> lr = Cells(Rows.Count, "B").End(xlUp).Row
> 'i assumed that column B was your second column. change if uneven columns
> Range("myrange").Offset(1, 1).Resize(lr - 1, 1).Select
> 
> you could use......
> Range("myrange").Offset(1, 0).Columns(2).Select
> 
> without the dim and without finding the last row but this would copy a blank 
> cell under the target column as well as the target column.
> 
> regards
> FSt1
> 
> 
> "Simon" wrote:
> 
> > Hi,
> > 
> > I have a range object which covers multiple columns and multiple rows.  The 
> > first row of the range object contains Header information so its just 
> > basically text, but I want to be able to copy the data underneath the 
> > headers.  My Range object is called NewGLData and I want to copy everything 
> > in the second column excluding the first row?  How can I do this? 
> > I tried using something like this:
> > NewGLData(Columns(2)).Select
> > Selection.Copy 
> > 
> > But I kept receiving a Type Mismatch error, if you have any advice as to how 
> > I can overcome this, that would be great.
> > 
> > Thanks.
> > Simon

hi
yes it does make a difference. you should have been more clear about that. 
ignore all code i posted. how is the user selecting the range? what would the 
user be inputing into the input box?

regards
FSt1


"Simon" wrote:

> Hi FSt1
> 
> Does it make a difference that my Range isn't a named range? the User 
> selects the range from an application input box which I've stored as a Range 
> object in my code, so rather than saying Range("myrange"), I could just use 
> Range(myrange) correct?
> 
> Thanks.
> Simon
> 
> "FSt1" wrote:
> 
> > hi
> > Dim lr As Long
> > lr = Cells(Rows.Count, "B").End(xlUp).Row
> > 'i assumed that column B was your second column. change if uneven columns
> > Range("myrange").Offset(1, 1).Resize(lr - 1, 1).Select
> > 
> > you could use......
> > Range("myrange").Offset(1, 0).Columns(2).Select
> > 
> > without the dim and without finding the last row but this would copy a blank 
> > cell under the target column as well as the target column.
> > 
> > regards
> > FSt1
> > 
> > 
> > "Simon" wrote:
> > 
> > > Hi,
> > > 
> > > I have a range object which covers multiple columns and multiple rows.  The 
> > > first row of the range object contains Header information so its just 
> > > basically text, but I want to be able to copy the data underneath the 
> > > headers.  My Range object is called NewGLData and I want to copy everything 
> > > in the second column excluding the first row?  How can I do this? 
> > > I tried using something like this:
> > > NewGLData(Columns(2)).Select
> > > Selection.Copy 
> > > 
> > > But I kept receiving a Type Mismatch error, if you have any advice as to how 
> > > I can overcome this, that would be great.
> > > 
> > > Thanks.
> > > Simon