Excel VBA Macro To Shift Up Data which is In Between Empty Cells in a Entire Sheet
'This Macro is Very Useful 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 Label1:
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"
Label1:
End Sub
Thanks,Tamatam
'This Macro is Very Useful 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 Label1:
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"
Label1:
End Sub
Thanks,Tamatam
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.