Forum Discussion
Solved
Shift cells up
Dear Experts, I have a data like below:- and want to delete all the nulls,( shift) all the empty cells up, I can do this using F5-> special , select blank and delete , but with h...
A formula approach:
=WRAPROWS(TOCOL(_0xB8D8_Log_Study_Sample,1),3)Or Power Query:
let Source = Excel.CurrentWorkbook(){[Name="_0xB8D8_Log_Study_Sample"]}[Content], #"Filled Down" = Table.FillDown(Source,{"Custom", "Custom.1"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Custom.2] <> null)) in #"Filtered Rows"
This VBA macro is very fast:
Sub ShiftUp()
Dim r As Long
Dim s As Long
Dim m As Long
Dim v
Application.ScreenUpdating = False
v = ActiveSheet.ListObjects(1).DataBodyRange.Value
m = UBound(v)
For r = 1 To m - 2 Step 3
s = r \ 3 + 1
v(s, 1) = v(r, 1)
If r > 1 Then v(r, 1) = ""
v(s, 2) = v(r + 1, 2)
v(r + 1, 2) = ""
v(s, 3) = v(r + 2, 3)
v(r + 2, 3) = ""
Next r
ActiveSheet.ListObjects(1).DataBodyRange.Value = v
Application.ScreenUpdating = True
End Sub
It runs OK for me in the attached version of your workbook (now a .xlsm).
