If statement to determine cells with data

ecarney14

New Member
Joined
Sep 21, 2006
Messages
42
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!
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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

New Member
Joined
Sep 21, 2006
Messages
42
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

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,993
Messages
5,545,378
Members
410,679
Latest member
rolandbianco
Top