Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Aug 09, 2025
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 huge data it Crash:-

Any solution which is less heavy and not cpu hungry that can do this job? any M code , or PQ UI steps?

I tried converting each row in to list and then List.RemoveNulls etc but didn't help..

Attached excel for Reference

Thanks in Advance,

Br,

Anupam

BI & Data Analysis
excel
Formulas and Functions
power bi
  • djclements's avatar
    djclements
    Aug 10, 2025

    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"

7 Replies

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Aug 10, 2025

    SQL:

    cli_filldown~import_csv~Custom,Custom1;
    select * from import_csv where Custom2<>'' 

  • djclements's avatar
    djclements
    Silver Contributor
    Aug 10, 2025

    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"
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 09, 2025

    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

     

Resources