Copying Non-Contiguous Range with a variable

[GIS]

Ok, So I was hoping to figure out the rest of this on my own, but I am stuck.

Currently, the code copies the entire row to the associated worksheet. What I am trying to do now is adjust the range so columns B through D are not copied as they are blank.

I have managed to adjust the range to it either copies just column A, or columns E over, but have not managed to make both happen at the same time. I have tried using extra parentheses and quotes, as well as Union with no success.

Here is the line that I currently have and it copies the entire row over.
Any suggestions?

.Range("A" & LSearchRow, "E" & LSearchRow & ":AF" & LSearchRow).Copy

 

[mirabeau]

Would like this be what you want?

Union(Range("A" & LSearchRow), Range("E" & LSearchRow), Range("AF" & LSearchRow)).Copy

Why do you have the colon before your AF?

 

[GIS]

Thank you for responding.

I have the colon in front of AF because I need all the data from columns E to AF in each row (LSearchRow) to copy over with the data in column A.
From what I have read I can specify this type of range using Range("A1, E1:AF1"). I was trying to duplicate this but it doesn't seem to work when I use the variable in place of row number. It just copies the entire row.

I hope this better explains the issue.

 

[mirabeau]

OK. Try

Union(Range("A" & LSearchRow), Range("E" & LSearchRow, "AF" & LSearchRow)).Copy

 

[GIS]

Hi,
Sorry for not responding earlier. I was out enjoying the sunshine all weekend!

Anyways, thank you for all your help. I was not able to get the union to work the way I wanted. .Union gave me an error each time and Union without the period in front pulled from the wrong sheet. I ended up finding a workaround. It's a bit clunky but does what I need. Thank you for your suggestions- I don't think I would have reached this point without your posts!

Here's the end result:

  .Range("A" & LSearchRow).Copy Worksheets("STD 8").Range("A" & Rows.Count).End(xlUp).Offset(1)
.Range("E" & LSearchRow, "AF" & LSearchRow).Copy Worksheets("STD 8").Range("B" & Rows.Count).End(xlUp).Offset(1)