Excel VB Macro - Shift left where blank cells

[Read_This]

Is there any way in a Excel VB macro where in column A there is data in most cells and, in the cells that are blank the data is in column B to delete the blank column A Cells, which in turn will shift left the data from column B cells – Ending up with all the data in column A and no blank cells. Ready for the VLOOKUP function.

For example:


[TABLE="width: 155"]
<tbody>[TR]
[TD]before
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]col A

[/TD]
[TD]col B
[/TD]
[/TR]
[TR]
[TD]apples
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]pears
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]oranges
[/TD]
[/TR]
[TR]
[TD]bananas
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]grapes
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]mangos
[/TD]
[/TR]
[TR]
[TD]strawberries


[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]after
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]col A

[/TD]
[TD]col B
[/TD]
[/TR]
[TR]
[TD]apples
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]pears
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]oranges
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bananas
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]grapes
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]mangos
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]strawberries
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[hiker95]

Read_This,

Sample raw data:

Excel 2007
	A	B
1	apples
2	pears
3		oranges
4	bananas
5	grapes
6		mangos
7	strawberries
8
Sheet1

After the macro:

Excel 2007
	A	B
1	apples
2	pears
3	oranges
4	bananas
5	grapes
6	mangos
7	strawberries
8
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ColumnsAB_ShiftfLeft()
' hiker95, 11/26/2014, ME820727
Dim lr As Long
lr = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
On Error Resume Next
Range("A1:B" & lr).SpecialCells(xlBlanks).Delete shift:=xlToLeft
On Error GoTo 0
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ColumnsAB_ShiftfLeft macro.

 

[hiker95]

Read_This,

Ready for the VLOOKUP function.

Just in case you had data to the right of column B that you did not want to move left, try the following:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub ColumnsAB_ShiftfLeft_V2()
' hiker95, 11/26/2014, ME820727
Dim a As Variant, i As Long
Dim lr As Long
lr = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
a = Range("A1:B" & lr)
For i = 1 To lr
  If a(i, 1) = "" Then
    a(i, 1) = a(i, 2)
    a(i, 2) = ""
  End If
Next i
Range("A1:B" & lr).ClearContents
Range("A1").Resize(lr, 2).Value = a
End Sub

Then run the ColumnsAB_ShiftfLeft_V2 macro.

 

[Read_This]

hiker95 - Thank you
I went with your first option @ 01:02pm

 

[hiker95]

Read_This,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.