End Copy Macro

Cardinal2 · #1 09-28-2017, 04:55 PM

I have a Keyboard macro that looks like below,
the macro unmerges a spreadsheet and then copies each column from A through J ending in row 10000,
The spreadsheet that is does this function may be have different number or data rows, from 1 row to any number.

I would like to change the macro so that once it reaches a row starting with column A and sees that there is no data in cells A through J on that
row, the copy command would stop. I would then be able to paste onto another spreadsheet rows that have data in them.

Cells.Select
With Selection

.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A9:J10000").Select
Selection.Copy
End Sub

Thanks for any help in adjusting the above macro
Cardinal 2

NoSparks · #2 09-29-2017, 06:16 AM

Assuming you don't have formulas forcing cells to display as blanks, replace this

Code:

Range("A9:J10000").Select
Selection.Copy

with

Code:

For i = 9 To 10001
    If Cells(i, "A") = "" Then
        If Application.CountA(Range("A" & i & ":J" & i)) = 0 Then
            Exit For
        End If
    End If
Next i
Range("A9:J" & i - 1).Copy

Cardinal2 · #3 10-01-2017, 04:57 PM

thanks for your help, it works perfectly
Cardinal 2

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Need Macro to Copy Notes from one PPT to another	mrayncrental	PowerPoint	15	10-19-2023 09:41 AM
Copy/Paste/LastRow Help with Macro	rsrasc	Excel Programming	1	09-24-2016 01:25 AM
Help With Copy Formula Down Macro	npalmer610	Excel Programming	4	03-14-2016 01:15 PM
a macro that can copy data from copy.xls to our current excel macro.xls based on criteria:	udhaya	Excel Programming	1	11-12-2015 10:12 AM
Macro to copy formula from one cell to another	anwar	Excel Programming	1	04-25-2014 08:27 PM