Macro to find last cell in row before blank

[Julie]

I want a macro that will find the last cell in row 16 before a blank.
Then I want to copy all the cells in the last three columns with data in
it from rows 16 - 200 and paste them in cells O16:Q200.
For example: if the last cell in row 16 before a blank is cell Z16, then
I want X16:Z200 copied and pasted special with values only into cells
O16:Q200.


** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Ron de Bruin]

Try this

Sub test()
Dim rng As Range
Set rng = Cells(16, Columns.Count).End(xlToLeft)
Set rng = Range(Cells(rng.Row, rng.Column), Cells(200, rng.Column - 2))
Range("O16:Q200").Value = rng.Value
rng.Select
End Sub

 

[Julie]

Ron,
Thanks for the help. Unfortunately, I have a formula in every third
cell and it is not counting that as a blank. I can change the formula
to say #N/A but then I would need it to copy the cells that are 3 back
from the #N/A. For example:
My data is:
R16=.2, S16=5, T16=1, U16=blank, V16=blank, W16=#N/A, X16=blank,
Y16=blank, Z16=#N/A, etc.
So I would want R16, S16, and T16 along with all the rows below it to
200 to be put in cells O16:Q200. Do you know how to do this?
Thanks so much!
Julie



** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Ron de Bruin]

Try this

It don't see a formula that evaluates to ""

Sub test()
Dim rng As Range
Dim Colnum As Integer
For Colnum = 256 To 1 Step -1
If Cells(16, Colnum).Value = "" Then
' do nothing
Else
Set rng = Cells(16, Colnum)
Exit For
End If
Next
Set rng = Range(Cells(rng.Row, rng.Column), Cells(200, rng.Column - 2))
Range("O16:Q200").Value = rng.Value
End Sub

 

[Julie]

Thanks Ron! It works!
Julie

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Ron de Bruin]

You are welcome
Thanks for the feedback