VBA to Remove Blank Range/cell (select columns) & Move Next Row Up

[WildBurrow]

I have a sheet with three groupings of data; REMOVED, RETURNED, & SUSPENDED. I'd like the User to be able to remove data from the 'RETURNED' group. If they do, columns F:H would become empty/blank.

I'd need to figure out how to find this blank section and move the next row of data up - without impacting the adjacent columns AND without bringing up the last visible row (27) which is a colored-filled border for the sheet.

I've tried several iterations of the following, such as:

 Range("F:F").SpecialCells(xlBlanks).Delete xlUp

or

 Range("F4:F26").SpecialCells(xlBlanks).Delete xlUp

but doing so pulled row 27 upward (which are color-filled cells which function as a border to the sheet) and removed the cell borders.

Falling short of using an iteration of the above then replacing cell borders and refilling color to row 27, is there a more concise method to use?

Finally, my apologies for attaching an image. I work for an organization that will not allow me to download XL2BB nor allow me access to Box.com or DropBox.com .

Thanks

 

[mumps]

Try:

Sub deleteBlankRange()
    Application.ScreenUpdating = False
    With Range("F27:H27")
        .Interior.ColorIndex = xlNone
        .Borders.LineStyle = xlNone
    End With
    Range("F4:H26").SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
    With Range("F27:H27")
        .Interior.ColorIndex = 3
        .Borders.LineStyle = xlContinuous
    End With
    Application.ScreenUpdating = True
End Sub

 

[WildBurrow]

Try:

Sub deleteBlankRange()
    Application.ScreenUpdating = False
    With Range("F27:H27")
        .Interior.ColorIndex = xlNone
        .Borders.LineStyle = xlNone
    End With
    Range("F4:H26").SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
    With Range("F27:H27")
        .Interior.ColorIndex = 3
        .Borders.LineStyle = xlContinuous
    End With
    Application.ScreenUpdating = True
End Sub

Mumps

Well, that is a lovely, concise piece of code. Remove the color, delete the blanks, replace the color and border. Thank you for providing a lesson on how to not 'over-think' the issue and for providing your time to reply to my question. Very much appreciated.

 

[mumps]

You are very welcome.