lundi 3 juin 2019

Exiting if statement without exiting sub

I have a VBA code that removes empty cells, allowing for data further down in the column to move up to the top. There are two things that I am looking to fix. One, I don't want to exit the sub, I want to exit the if statement. I've read that you can exit loops, whiles, fors, etc. But not ifs. I don't want to exit the sub because I have more code in my sub. Any suggestions?

I am also having issues adding another line in the code to run if there is already data at the top of the column.

    Sub Remove_Empties ()

    Dim Last as Long
    Dim Mcol as Range

    Last = Cells(Rows.Count, "AD").End(xlUp).Row
    If Last = 1 Then Exit Sub 'no value only header in row 1
    'otherwise
    Set Mcol = Range("AD2:AD" & Last) 'contains any value
    Application.ScreenUpdating = True
    Mcol.SpecialCells(xlCellTypeBlanks).Delete xlUp
    Application.ScreenUpdating = True

    End Sub

I want it to be able to run the following three scenarios. The first contains data in the row below the header. The second has a couple of empty cells. The third has all empty cells. 1 should not change, 2 should change to match 1's format, and 3 should do nothing. 2 and 3 can be done with the code above, but not 1.

    1         2         3
    A       
    B  
    C         A
    D         B
              C
              D

Aucun commentaire:

Enregistrer un commentaire