Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-28-2017, 04:55 PM
Cardinal2 Cardinal2 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Feb 2013
Posts: 10
Cardinal2 is on a distinguished road
Smile End Copy Macro

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
Reply With Quote
  #2  
Old 09-29-2017, 06:16 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Posts: 388
NoSparks is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 10-01-2017, 04:57 PM
Cardinal2 Cardinal2 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Feb 2013
Posts: 10
Cardinal2 is on a distinguished road
Thumbs up End copy macro

thanks for your help, it works perfectly
Cardinal 2
Reply With Quote
Reply
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
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
Need Macro to Copy Notes from one PPT to another mrayncrental PowerPoint 2 10-08-2014 02:13 PM
Macro to copy formula from one cell to another anwar Excel Programming 1 04-25-2014 08:27 PM


All times are GMT -7. The time now is 03:25 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft