Select using character as reference

[hazzaska]

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 -

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

 

[oldbrewer]

a	b	c	d
e	f	g	h
-	p	q	r
	assume only 4 columns (A to D)
	for j=1 to 100
	jj=jj+1
	if cells(j,1)="-" then mylastrow = jj-1:goto 999
	next j
	999 end sub
	so can you just plug "mylastrow" into your macro ?

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Select until character
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[hazzaska]

a	b	c	d
e	f	g	h
-	p	q	r
	assume only 4 columns (A to D)
	for j=1 to 100
	jj=jj+1
	if cells(j,1)="-" then mylastrow = jj-1:goto 999
	next j
	999 end sub
	so can you just plug "mylastrow" into your macro ?

Im sorry but i am a newbie really that has gone right over my head?

 

[hazzaska]

Cross posted here Select until character (excelforum.com)

 

[jolivanes]

Change references where required. The 2nd example is set for Column B
With spaces between blocks of data as per your example.

Sub Transfer_Blocks_Col_A()
Dim a As Long, b As Long, lr As Long
a = 2
lr = Cells(Rows.Count, 1).End(xlUp).Row
    Do Until b = lr
        b = Range("A" & a & ":A" & lr).Find("*-*").Row
            Range(Cells(a, 1), Cells(b, 1)).Copy Sheets("Sheet2").Cells(1, Sheets("Sheet2").Columns.Count).End(xlToLeft).Offset(, 1)
        a = Cells(b, 1).End(xlDown).Row
    Loop
End Sub

Without any spaces between blocks of data.

Sub Transfer_Blocks_Col_B()
Dim a As Long, b As Long, lr As Long
a = 2
lr = Cells(Rows.Count, 2).End(xlUp).Row
    Do Until b = lr
        b = Range("B" & a & ":B" & lr).Find("*-*").Row
            Range(Cells(a, 2), Cells(b, 2)).Copy Sheets("Sheet2").Cells(1, Sheets("Sheet2").Columns.Count).End(xlToLeft).Offset(, 1)
        a = b + 1
    Loop
End Sub

 

[hazzaska]

Thank you that worked for me