Hi
Could someone help in tweaking my macro, Currently it selects "a1 to x7" and runs which if fine unless my data has less lines, The data i have always finishes with an hyphen on the row below the data i require to copy. I need my macro to select the range upto and including the first instance of the hyphen if thats possible.
Any help greatly appreciated
Works
A1 02/09/2021
A2 02/09/2021
A3 02/09/2021
A4 02/09/2021
A5 02/09/2021
A6 02/09/2021
A7 -
Doesn't work
A1 02/09/2021
A2 02/09/2021
A3 02/09/2021
A4 02/09/2021
A5 -
A1 02/09/2021
A2 02/09/2021
A3 02/09/2021
A4 02/09/2021
A5 02/09/2021
A6 02/09/2021
A7 -
Could someone help in tweaking my macro, Currently it selects "a1 to x7" and runs which if fine unless my data has less lines, The data i have always finishes with an hyphen on the row below the data i require to copy. I need my macro to select the range upto and including the first instance of the hyphen if thats possible.
Any help greatly appreciated
Works
A1 02/09/2021
A2 02/09/2021
A3 02/09/2021
A4 02/09/2021
A5 02/09/2021
A6 02/09/2021
A7 -
Doesn't work
A1 02/09/2021
A2 02/09/2021
A3 02/09/2021
A4 02/09/2021
A5 -
A1 02/09/2021
A2 02/09/2021
A3 02/09/2021
A4 02/09/2021
A5 02/09/2021
A6 02/09/2021
A7 -
VBA Code:
My Macro
Sub Collect()
'
' Macro5 Macro
'
Application.ScreenUpdating = False
'Let x = 0
Do While X < Sheets("PasteDataHere").Range("ag1").Value
Sheets("PasteDataHere").Select
Range("A1:x7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("PasteDataHere").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Sheets("Sheet3").Select
Range("M1").Select
Call GreyCalc
X = X + 0
Loop
End Sub