Saturday, 8 December 2012

Excel VBA Macro To ShiftUp Data Which In Between Empty Cells in a Entire Sheet

Excel VBA Macro To ShiftUp Data Which In Between Empty Cells in a Entire Sheet
'This Macro is Very Usefull to Shift Up The Data Of a From All Rows and Columns , Where Data Is In Between Empty Rows
'Data In Between Empty Rows Means Some Rows Having Data and Some Rows 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 Rows and Columns as It is In Same Order.

Sub Shift_Data_Upp()
Dim X As Long
Dim Y As Long
Dim C As Long

Dim RC As Long
Dim cc As Long

On Error GoTo TPR:

RC = Cells(1, 1).EntireColumn.Cells.COUNT
cc = Cells(1, 1).EntireRow.Cells.COUNT

'RC Is The Total No.Of Rows Count(1048576)
'CC Is The Total No.Of Columns Count(16384)

'If You Know The Rows Range(Eg: 100) Define It So That You Can Speed Up Macro Process
'If You Know The Columns Range(Eg: 26) Define It So That You Can Speed Up Macro Process

Y = 0

For X = 1 To RC  'Instead of 'RC' You Can Give Your Desired Range
If Cells(X, 1) <> "" Then
Y = Y + 1

For C = 1 To 26 'Instead of '26' You Can Give Your Desired Range
'You Can Use 'CC' For All Columns But It Takes So Much Time

Sheets(1).Cells(Y, C) = Sheets(1).Cells(X, C)
'Shifts Up Data From All Rows of Sheet1  As It Is.
Next C
End If
Next X

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

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

TPR:
End Sub

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts