VBA deplace a cells into column A

[cazdealer]

Hi,
I need to create a VBA procedure that will cut and paste (or deplace) all my filled cells into column A.

For exemple, I have a lot of empty cell in the range A1 to BF999 but also have cells with data in them.

I would like to take every cell that have data in that range and put them from cell A1 to A999999. (in whatever order). And do nothing with the empty cells.

how could I do that?

thanks

 

[Gary McMaster]

Maybe the "SpecialCells" method?

Gary

Public Sub Test()

Dim oCurrCell As Range
Dim oCell As Range
Dim oData As Range

Set oCurrCell = ActiveSheet.Range("A1")

Set oData = ActiveSheet.Range("A1:BF999").SpecialCells(xlCellTypeConstants)

For Each oCell In oData
    oCurrCell.Value = oCell.Value
    oCell.Interior.ColorIndex = 3
    Set oCurrCell = oCurrCell.Offset(1, 0)
Next oCell

Set oData = ActiveSheet.Range("A1:BF999").SpecialCells(xlCellTypeFormulas)

For Each oCell In oData
    oCurrCell.Value = oCell.Value
    oCell.Interior.ColorIndex = 4
    Set oCurrCell = oCurrCell.Offset(1, 0)
Next oCell

End Sub

 

[cazdealer]

it doesnt works...
it buggs at line:

Set oData = ActiveSheet.Range("A1:BF999").SpecialCells(xlCellTypeFormulas)

 

[Gary McMaster]

Apparently you have no formulas. Try with formula section disabled like this:

Gary

Public Sub Test()

Dim oCurrCell As Range
Dim oCell As Range
Dim oData As Range

Set oCurrCell = ActiveSheet.Range("A1")

Set oData = ActiveSheet.Range("A1:BF999").SpecialCells(xlCellTypeConstants)

For Each oCell In oData
    oCurrCell.Value = oCell.Value
    oCell.Interior.ColorIndex = 3
    Set oCurrCell = oCurrCell.Offset(1, 0)
Next oCell

'Set oData = ActiveSheet.Range("A1:BF999").SpecialCells(xlCellTypeFormulas)
'
'For Each oCell In oData
'    oCurrCell.Value = oCell.Value
'    oCell.Interior.ColorIndex = 4
'    Set oCurrCell = oCurrCell.Offset(1, 0)
'Next oCell


End Sub

 

[Gary McMaster]

BTW, if you did have formulas and they were in column A data might be lost (overwritten) by the first loop. It would be better to insert a new column in front of column A and change the range(s) from ActiveSheet.Range("A1:BF999") to ActiveSheet.Range("B1:BG999").

Gary