Using a range of non-empty cells in VBA

[bbot23]

Hey guys,

I have a simple line of code in VBA which I need to modify:

Workbooks("trend_strat").Worksheets("recordings").Range("E2:E23").Value = Workbooks("trend_strat").Worksheets("recordings").Range("D2:D23").Value

Right now it goes from row 2 to 23 because those are the cells with values. I'd like the range to be from 2 to the last cell with a value above 0. For example if D24:D44 had no value but D45 had a value and cells after that didn't, the range would be D2:D45 and E2:E45.

Is that possible? Thanks guys!!

 

[Hamburgler]

It is definitely possible. Here is an messy way that should do it.

LastRow = Cells(Rows.Count,4).End(xlUp).Row
Set MyRange = Cells(2,4).Resize(LastRow-1,1)
For Each X In MyRange
    If Isnumeric(X.Value) and X.Value > 0 Then
        Set Y = X
    Endif
Next X
Set MyRange = Range(Cells(2,4),Y)
MyRange.Offset(0,1).Value = MyRange.Value