PDA

View Full Version : Solved: CELLS W/ NO VALUE NOT EQUAL TO BLANK



alan23jm
03-31-2008, 04:02 AM
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
03-31-2008, 04:07 AM
Can you post an example workbook and tell us where it would end up?

alan23jm
03-31-2008, 04:37 AM
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


8295

Bob Phillips
03-31-2008, 05:03 AM
There are not enough blank columns to move columns L:S into, so where would you expect it to go?

alan23jm
03-31-2008, 05:34 AM
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
03-31-2008, 05:50 AM
Even though Phone 1 is not blank, it has a zero? and what about the zero in Phone 8 on row 4.

alan23jm
03-31-2008, 05:56 AM
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
03-31-2008, 06:15 AM
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

alan23jm
03-31-2008, 06:45 AM
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
03-31-2008, 08:14 AM
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.