Adapt VBA code to Copy only cells with Data

**ShakJames** · 09-29-2016, 11:25 AM

Hi all,

I was wondering if someone could help me out with this. I have the below code, which I'd like to adapt. At the moment it copies all data from cells A2:E500. However, what I'd like it to do is copy only the cells with data in those columns, so basically select and copy only the cells with data in it. So for example if the data only goes down to A20 then I only want it to copy A2:E20 and not A2:E500.

Can someone help!

Dim lst As Long

Sheets("Email Check").Range("A:E").Copy

With Sheets("KYC to Check")
    lst = .Range("A" & Rows.Count).End(xlUp).Row + 1
    .Range("A" & lst).PasteSpecial xlPasteColumnWidths
    .Range("A" & lst).PasteSpecial xlPasteValues

Thanks!

**JOHN H. DAVIS** · 09-29-2016, 11:34 AM

Maybe:

Sub ShakJames()
Dim x As Long
With Sheets("Email Check")
x = .Range("A2").End(xlDown).row
    .Range("A2:E" & x).Copy
End With
End Sub

**ShakJames** · 09-29-2016, 11:41 AM

Cheers, John

That's done that job.

Thanks for the help. Much appreciated.

**JOHN H. DAVIS** · 09-29-2016, 11:45 AM

You're welcome. Glad to help out and thanks for the feedback.

Adapt VBA code to Copy only cells with Data

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Adapt VBA code to Copy only cells with Data

Re: Adapt VBA code to Copy only cells with Data

Re: Adapt VBA code to Copy only cells with Data

Re: Adapt VBA code to Copy only cells with Data

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Adapt my current VBA Code to update pivot based selection in 2 cells

[SOLVED] Adapt Code to copy and paste multiple Ws to multiples Ws to another Wb

adapt VBA code to stop and alert if any cells are blank

How to adapt this code to copy unique values in cells

[SOLVED] How to adapt a code to include a range of cells instead of the whole sheet

[SOLVED] Adapt VBA code to clear the related cells instead of update

[SOLVED] Adapt code to copy and paste

Bookmarks

Bookmarks

Posting Permissions