VBA code needed to copy data

[sooshil]

Hi all

I have some data that ranges A28:J37. I don't always have all that range full.
Sometimes I will have A28:J30, sometimes A28:J34 and so on.
The column A will be filled with "" upto A37 when there are no data.

Now my requirement is I need a macro that copies only that range A28 to Jx where x is the last row that has some value other than "" in column A.
I just need to check column A not having "". And, I need that copied values to be pasted as values in another sheet.

for example,

column A
abc(A28)
def(A29)
ghi(A30)
""
""
""
""
and so on upto A37
Now, the macro should copy A28:J30 and paste as values in another sheet.
Next time, if A31 also has values other than "", then it should copy A28:J31 and paste as values in another sheet.
I can configure the destination sheet in the code.

Thank you.

 

[Fluff]

Does col A have a formula that returns "", or is the "" hard values?

 

[JoeMo]

Change the paste sheet and range to suit:

Sub sooshil()
'assumes this code is run from Sheet1 and paste is to sheet2
Dim R As Range, lR As Long
Set R = Range("A28:J37")
lR = Range("A28:J37").Columns(1).Find("*", LookIn:=xlValues, searchdirection:=xlPrevious).Row
Range("A28:J" & lR).Copy
With Sheets("Sheet2")
    .Range("A1").PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
End Sub

 

[sooshil]

Does col A have a formula that returns "", or is the "" hard values?

Every cell within the range has formula. So "" returned in column A is also by a formula.

 

[Fluff]

In that case try JoeMo's code

 

[sooshil]

Change the paste sheet and range to suit:

Sub sooshil()
'assumes this code is run from Sheet1 and paste is to sheet2
Dim R As Range, lR As Long
Set R = Range("A28:J37")
lR = Range("A28:J37").Columns(1).Find("*", LookIn:=xlValues, searchdirection:=xlPrevious).Row
Range("A28:J" & lR).Copy
With Sheets("Sheet2")
    .Range("A1").PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
End Sub

Thanks JoeMo for your awesome code.
I want to keep the destination data be selected after pasting. Now, the whole 10 rows is being selected regardless of empty rows. But, I want to select only those rows which has values.
Means, if we pasted A28:J33 then after pasting, I want only those six rows be selected. But, current code selects 10 rows.

 

[JoeMo]

Thanks JoeMo for your awesome code.
I want to keep the destination data be selected after pasting. Now, the whole 10 rows is being selected regardless of empty rows. But, I want to select only those rows which has values.
Means, if we pasted A28:J33 then after pasting, I want only those six rows be selected. But, current code selects 10 rows.

The code I posted will select only the pasted range. So, if 6 rows were pasted, only those 6 rows would be selected on the destination sheet.