Saturday, 8 December 2012

How To ShiftUp Data In Between Empty Cells in a Desired Column using Excel VBA Macro

Excel VBA Macro To ShiftUp Data Which In Between Empty Cells in a Desired Column
'This Macro is Very Usefull to Shift Up The Data Of a Desired Column , Where Data Is In Between Empty Cells
'Data In Between Empty Cells Means Some Cells Having Data and Some Cells are Empty
'This Macro Does not Delete Any Cells So That the Data in Other Columns Would Not Distrub
'This Macro Shifts Up Data From All Cells(1048576 Rows)of a Desired Column

Sub Shift_Data_Up()
Dim X As Long
Dim Y As Long
Dim z As Long

Dim C As Long
On Error GoTo TPR:

C = InputBox("Enter Desired Column No.In Which Data To Be Shift UP", _
"Shift Up Data In Between Empty Cells", _
"Enter Here As 1 or 2 or 3.....")

z = Cells(1, C).EntireColumn.Cells.COUNT

'Z Is The Total No.Of Rows(1048576)
'If You Know The Row Range(Eg: 100) Define It So That You Can Speed Up Macro Process

Y = 0

For X = 1 To z  'Instead of 'Z' You Can Give Your Desired Range
If Cells(X, C) <> "" Then
Y = Y + 1
Sheets(1).Cells(Y, C) = Sheets(1).Cells(X, C)
'Shifts Up Data From Desired Column of Sheet1
End If
Next X

'Range(Cells(Y + 1, C), Cells((Y + 1) + (Z - (Y + 1)), C)).Clear
'Clears The Data In Following Cells Of Shifted Data

MsgBox "Data Success Fully Shifted Up", vbInformation, "Macro Process Completed"

End Sub

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts