Consulting

Results 1 to 7 of 7

Thread: remove rows and columns

  1. #1
    VBAX Newbie
    Joined
    Jan 2021
    Location
    Berlin
    Posts
    5
    Location

    remove rows and columns

    hello guys

    Please write a subroutine that clears the empty rows and columns. After running your codes, you should have a table like the one shown below


    2131.jpg


    can u help me
    how can i get the first table like the second ?
    Ty very much

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,193
    Homework? Assignment?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Newbie
    Joined
    Jan 2021
    Posts
    4
    Location
    Hello,

    i am thinking whats best way for your solution. U can do loops to check if range is empty and then VBA decide to delete rows or columns.
    Note Set rng and arng u can change it not sure do you have small lists or insane huge u can cap it at around 10000 if you never go above 10000 range same for collumns i did locked vba code to BB1 not sure do you go further. Also i saw your list start with 2x empty row so i made one more check for first row is it empty or not at end. and messasge at end u can delete MSGBOX have nice day !

    Sub kokycar()
    Application.ScreenUpdating = False
    Dim rng As Range, cell As Range
    Set rng = Range("A1:A10000")
    For Each cell In rng
    cell.Select
    If Application.CountA(ActiveCell.EntireRow) = 0 Then
        cell.EntireRow.Delete
    End If
    Next cell
    Dim arng As Range, acell As Range
    Set arng = Range("A1:BB1")
    For Each acell In arng
    acell.Select
    If Application.CountA(ActiveCell.EntireColumn) = 0 Then
        acell.EntireColumn.Delete
    End If
    Next acell
    Application.ScreenUpdating = True
    If Application.CountA(Range("A1").EntireRow) = 0 Then
        Range("A1").EntireRow.Delete
    End If
    MsgBox "Done"
    End Sub
    Last edited by iivansi2; 01-22-2021 at 06:53 AM.

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,090
    @iivansi2

    2 lines of VBA-code suffice.

    Man kann keine Zeilen oder Spalten löschen; rows.count und columns.count sind Konstante.

  5. #5
    VBAX Newbie
    Joined
    Jan 2021
    Posts
    4
    Location
    omg you are right i tried to goole it hehe

    On Error Resume Next   
     Columns("b").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
     Rows("1").SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
    note this macro checks is collumn B empty so if your list is constant and data is like u presented starts in column b it will sort list properly

  6. #6
    VBAX Newbie
    Joined
    Jan 2021
    Location
    Berlin
    Posts
    5
    Location
    Sub DeleteRows()

    Dim cell As Range

    For Each cell In Range("a1:AA350")
    If Application.WorksheetFunction.CountA(cell.EntireRow) = 0 Then
    cell.EntireRow.Delete
    End If
    Next cell

    End Sub
    Sub DeleteColumns()

    Dim cell As Range

    For Each cell In Range("a1:z340")
    If Application.WorksheetFunction.CountA(cell.EntireColumn) = 0 Then
    cell.EntireColumn.Delete
    End If
    Next cell

    End Sub
    Actually solved with this
    Ty very much

  7. #7
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,090
    Why don't you use the suggestion in #5 ?

    Why don't you use code tags ?

    Where did you find this code ? Are you a crossposter ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •