Saturday, December 8, 2012

Excel VBA Macro to Shift Up Data which is in Between Empty Cells in a Entire Sheet

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

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.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog