Simple VBA script to copy data in column until text reached

[yoptoo]

Hello

Very simply.. I have a column of text (usually ranging from A1:A250) that ends with an asterisks ( * )
A VBA script attached to a macro button that when clicked, copies to clipboard everything in the column up to the asterisk would be perfect
This can then be pasted into notepad
What I am specifically trying to avoid are empty lines after the asterisks - the VBA script would need to know to not copy anything after the asterisks has been reached. This is why a simple copy A1:A250 doesn't work


Picture attached explains it best

I am only just getting my into VBA so I am still at the beginning

Thanks

 

[igold]

errant post

 

[yoptoo]

Any thoughts on how this could be achieved?
Also acceptable would be for a VBA script to copy all cells in a column that contain data and stop when an empty cell is found..

 

[Peter_SSs]

Is there ever anything below the asterisk?
If not, what about

Sub Copy_Data_1()
  Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(-1)).Copy
End Sub

To copy from top to last value

Sub Copy_Data_2()
  Range("A1", Range("A" & Rows.Count).End(xlUp)).Copy
End Sub

 

[yoptoo]

Unfortunately this still copies the empty lines that may be present between the data and the asterisk

 

[yoptoo]

It is worth noting that the data are all references to cells in another sheet

E.g
='Sheet2'!A1
='Sheet2'!A2
='Sheet2'!A3
='Sheet2'!A4

and so on to A150
Some cells return blank values - these are the ones I want to exclude, however your code includes them and copies them so when pasted into clipboard, there are lots of blank lines

 

[yoptoo]

Figured out a way to do what I wanted; basically set the value of the cells with data into another set of adjacent cells, then copy that data to clipboard using your line of code - blank spaces are ignored.

Sub Copy()
Range("B1:B220").Delete
Range("B1:B220").Value = Range("A1:A220").Value
Range("B1", Range("B" & Rows.Count).End(xlUp)).Copy
End Sub

Perfect!
Thanks

 

[Peter_SSs]

Unfortunately this still copies the empty lines that may be present between the data and the asterisk

Can you expand on that? In your original image there are no "empty lines" between the data and the asterisk and there was no mention of that in the original description.

It is worth noting that the data are all references to cells in another sheet

You also did not tell us that the "empty" cells contain formulas - which of course means that the cell are not in fact empty.

Try this to copy from row 1 to the row immediately above the asterisk

Sub Copy_Data_3()
  Range("A1", Columns("A").Find(What:="?*", LookIn:=xlValues, SearchDirection:=xlPrevious).Offset(-1)).Copy
End Sub

To copy from row 1 to the last row with anything

Sub Copy_Data_4()
  Range("A1", Columns("A").Find(What:="?*", LookIn:=xlValues, SearchDirection:=xlPrevious)).Copy
End Sub