If statement to determine cells with data

[ecarney14]

I am trying to creat an if statement at the beginning of a long set of data. I want it to search the column, and only execute the macro if there is data in each cell, if not, skip column and go to the next column of data.

here's a simple form of a macro for one column, but it will be doing 45 columns of this

Sub B()

Dim iMax As Double, iFMax As Double, iMin As Double, lRowMax As Long
iMax = Application.WorksheetFunction.Max(Range("B2:B20000"))
lRowMax = Range("B2:B20000").Find(iMax).Row
iMin = Application.WorksheetFunction.Min(Range("B2" & ":B" & lRowMax - 1))



Dim rAW As Range
For Each rAW In Range("b2:b20000")
rAW.Offset(, 47) = rAW - iMin

Next

End Sub



Thanks for any help!

 

[onlyadrafter]

Hello,

a couple of questions -

will there be any gaps in the data? i.e. if Column A has row 20 as the end, will all cells above this have data?

which columns are you using?

 

[ecarney14]

will there be any gaps in the data? i.e. if Column A has row 20 as the end, will all cells above this have data?

there should not be any gaps in the data. So yes, all the cells above should have data in them

I am hoping to allow the space for 48 columns of data, and the macro makes an additional 48 columns (obviously if the original data allows it) so techincally. Columns B-CS

Hope that helps! Thanks

 

[onlyadrafter]

Hello,

This is long winded, but seems to work, will look at improving

Sub Columns()
For MY_COLS = 2 To Range("AW2").End(xlToLeft).Column
    FIRST_CELL = Range("A1").Offset(0, MY_COLS - 1).Address
    SECOND_CELL = Range("A65536").Offset(0, 1).End(xlUp).Address
    iMax = Application.WorksheetFunction.Max(Range(FIRST_CELL, SECOND_CELL))
    lRowMax = Range(FIRST_CELL, SECOND_CELL).Find(iMax).Row
    iMin = Application.WorksheetFunction.Min(Range(FIRST_CELL, Range("A1").Offset(lRowMax - 1, MY_COLS)))

    For MY_ROWS = 2 To Range("A65536").Offset(0, MY_COLS - 1).End(xlUp).Row
        Range("A1").Offset(MY_ROWS - 1, 46 + MY_COLS) = Range("A1").Offset(MY_ROWS - 1, MY_COLS - 1).Value - iMin
    Next MY_ROWS
Next MY_COLS
End Sub

 

[ecarney14]

thanks! i will fumble around with it...