Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-28-2017, 04:55 PM
Cardinal2 Cardinal2 is offline End Copy Macro Windows 7 64bit End Copy Macro Office 2007
Novice
End Copy Macro
 
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 End Copy Macro Windows 7 64bit End Copy Macro Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
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 End Copy Macro Windows 7 64bit End Copy Macro Office 2007
Novice
End Copy Macro
 
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

Thread Tools
Display Modes


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
End Copy Macro 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

Other Forums: Access Forums

All times are GMT -7. The time now is 06:17 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft