Select using character as reference

hazzaska

New Member
Joined
Jan 8, 2012
Messages
36
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 -


File-Copy-icon.png

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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,004
abcd
efgh
-pqr
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 8, 2012
Messages
36
abcd
efgh
-pqr
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?
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,709
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Change references where required. The 2nd example is set for Column B
With spaces between blocks of data as per your example.
Code:
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.
Code:
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,663
Messages
5,626,166
Members
416,166
Latest member
Archimed

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top