Solved: CELLS W/ NO VALUE NOT EQUAL TO BLANK

[alan23jm]

Hi Everyone,

Can u please help me with a code that will move data from a column to the next available column to the left? I understand the offset command will do but since the data came from a csv file converted to excel, the supposedly "blank cells" has values which are not visible thus excel don't treat the cells as blank. I have 50,000 rows to work on to rearrange the data move to the left. Also, if it is not too much, how can I move data to a cell if the value of the cell to transfer data to is "0". I would appreciate the help anyone can extend. Thanks!

[Bob Phillips]

Can you post an example workbook and tell us where it would end up?

[alan23jm]

here's the sample code from macro recorder:

Range("L:S").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlToLeft


Attachment 8295

[Bob Phillips]

There are not enough blank columns to move columns L:S into, so where would you expect it to go?

[alan23jm]

I want all phone data to move to the next available column to the left. Looking at row 2 for example: there are 3 phone datas on that row but are positioned apart (under column "phone 2", "phone 3", and "phone 8". I need them to move to the left so they will fill columns for "phone 1", "phone 2", and "phone 3". I have a userform where this data needs to be transferred that is why I need to rearrange the data in order. The code should loop through the rows to rearrange the data until the last row.

[Bob Phillips]

Even though Phone 1 is not blank, it has a zero? and what about the zero in Phone 8 on row 4.

[alan23jm]

I actually need to overwrite all the zeros with the next info on its right. Since "phone 8" is the last record, it can be transferred on the next available column to its left.

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long, j As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 2 To LastRow

For j = 19 To 13 Step -1

If Trim(.Cells(i, j - 1).Value) = "" Or .Cells(i, j - 1).Value = 0 Then

.Cells(i, j).Resize(, 20 - j).Cut .Cells(i, j - 1)
End If
Next j
Next i
End With

End Sub
[/vba]

[alan23jm]

xld,

It worked great! This is exactly what I needed! You're really a genius!
But one quick question, Const TEST_COLUMN As String = "A" , what does this do exactly and what other values can substitute "A"?

[Bob Phillips]

That was there to identify the key column, the one that the code works off of to determine where the last row is. In other words, which column has the key data and therefore will be populated for all rows.