copying up to blank cells

[Matthew McManus]

Hi,

I hope someone can help me with some code for this problem

I have data (text and numbers) in columns A to D eg.

A B C D
1 tr 5 4 a
2 5 t 3 2
3 t 2 a 1
4 2 3
5

What I want to be able to do is to copy the array until I reach a ro
where all the values in columns a to d are blank. In the above example
to copy A14

I then want to paste this to another worksheet, again in columns a t
d. This worksheet will already contain data, so I want to do a searc
in these columns for the last data entered, leave a blank line and the
paste.

I have searched the forum for information on finding blank ranges, bu
nothing seemed to fit this problem.

Thanks for any assistance.

Best wishes
Matthe

 

[Norman Jones]

Hi Mathew,

If the corresponding cells in column are blank, E1:E5 in your example data,
then try:

Sub Tester()

Dim WS1 As Worksheet, WS2 As Worksheet
Dim i As Long
Dim Lastcell As Range

Set WS1 = ActiveWorkbook.Worksheets("Sheet1") ' <<< CHANGE
Set WS2 = ActiveWorkbook.Worksheets("Sheet2")' <<< CHANGE
Set Lastcell = WS2.Cells(Rows.Count, 1).End(xlUp)

WS1.Range("A1").CurrentRegion.Copy Destination:=Lastcell(2)

End Sub

Adjust the worksheet names to match your requirements.

 

[Norman Jones]

Hi Mathew,

If the corresponding cells in column are blank, E1:E5 in your example

data,

should have read:

If the corresponding cells in column E are blank, E1:E5 in your example

data,