Deleting blank rows in range only...

[RobbieC]

Hi there, I have a range (BA103:DA114) and I am looking for a simple way to remove any blank rows. However, I only need this range of 10 rows to be in the equation as there are other cells to the left and right and also below this range which contain data... (if you get my drift)

I can delete entire rows using :

Sub DeleteBlanks()
Dim intCol As Integer
With Worksheets("Sheet1")
     
    For intCol = 1 To 20 'cols A to change for number of columns
        .Range(.Cells(1, intCol), .Cells(10, intCol)). _
        SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
    Next intCol
    
End With
End Sub

but this code not only deletes the blank rows in the range, but anything below in the column is shifted up by the number of rows deleted.

I only want this code to affect the range (BA103:DA114) and nothing to left or right or below...

If you can point me in the right direction, I'd be very grateful

Thanks

 

[JackDanIce]

You can prevent affecting cells to the left and right, but if you delete a value, the default action is to shift the cells up, so you will always be adjusting the data below your stated range.

Can you explain with more detail, what you mean by removing blank rows, given your code nearly appears to do what you're wanting? Maybe with a before and after example.

At a guess, this might be what you want:

Sub DelBlanks

Sheets("Sheet1").Range("BA103:DA114")SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp

End Sub

 

[RobbieC]

Thanks JackDan, I understand what you mean. Your code works great

Sub DelBlanks

Sheets("Sheet1").Range("BA103:DA114").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp

End Sub

but I was hoping that I could avoid any rows beneath being affected. ie. only moving the cells in the range.

I will go with the above code and have a think about rearranging my sheet so that there is nothig beneath...

Thanks very much for your help

 

[JackDanIce]

You could copy the range to a blank sheet, remove the blank cells then copy the same sized range (but with blank cells deleted) back to the source sheet if you needed to stick to same layout on source sheet

 

[My Aswer Is This]

Why not just clear the cell and not delete the cell. Clearing the cell does not effect the cells below?

Thanks JackDan, I understand what you mean. Your code works great

Sub DelBlanks

Sheets("Sheet1").Range("BA103:DA114").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp

End Sub

but I was hoping that I could avoid any rows beneath being affected. ie. only moving the cells in the range.

I will go with the above code and have a think about rearranging my sheet so that there is nothig beneath...

Thanks very much for your help

 

[JackDanIce]

Not sure OP wants to clear a blank cell

 

[JackDanIce]

Relating to reply #4 , try:

Sub DeleteBlanks()        
    Dim rng As Range
    Dim wks As Worksheet
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    
    Set rng = Sheets("Sheet1").Range("BA103:DA114")
    Set wks = Worksheets.add(after:=Sheets(Worksheets.count))
    
    With wks
        With .Cells(1, 1).Resize(rng.Rows.count, rng.Columns.count)
            .Value = rng.Value
            .SpecialCells(xlCellTypeBlanks).Delete xlUp
        End With
        Set rng = .Cells(1, 1).Resize(rng.Rows.count, rng.Columns.count)
    End With
    
    With Sheets("Sheet1")
        .Range("BA103:DA114").Value = rng.Value
        .Select
    End With
    
    wks.Delete
    
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    
    Set rng = Nothing
    Set wks = Nothing
            
End Sub

Alternatively, you could insert a helper column at DE, use some kind of logic to fill values in DE103:DE114 and then sort the range BA103:DE114 to "push" the blank cells to the bottom of the range but this may be tricker to get the output you want, depending on what the data is and how it is sorted.

Or yes, as you say, change Sheet1's design and layout!

 

[tonyyy]

Another approach... just insert the equivalent number of rows that were deleted to the end of the range...

Sub DelBlanks_1025997()
Dim last As Long, kount As Long, rw As Long, rng As Range
Set rng = ActiveSheet.Range("BA103:DA114")

last = rng.Rows.Count
''''Determine total blank rows; delete blank rows
For rw = rng.Rows.Count To 1 Step -1
    If WorksheetFunction.CountA(rng.Rows(rw)) = 0 Then
        kount = kount + 1
        rng.Rows(rw).Delete
    End If
Next rw

''''Insert equivalent number of rows below
rng.Rows(last - kount + 1 & ":" & last).Insert
End Sub

Cheers,

tonyyy

 

[Peter_SSs]

If the range in question only contains constants and blanks, or any formulas in that range are no longer required, just their values you could try this in a copy of your workbook to see if it does what you want

Sub Move_Up()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, lr As Long
  
  With Range("BA103:BE114")
    a = .Value
    lr = UBound(a) + 1
    ReDim b(1 To lr, 1 To UBound(a, 2))
    For i = 1 To UBound(a)
      For j = 1 To UBound(a, 2)
        If Len(a(i, j)) > 0 Then
          b(lr, j) = b(lr, j) + 1
          b(b(lr, j), j) = a(i, j)
        End If
      Next j
    Next i
    .Value = b
  End With
End Sub

 

[JackDanIce]

@RobbieC - curious, the output of code suggested in #8 differs from that in #7 and #9 .

Whilst none of the 3 suggestions suggest clearing the contents of a blank cell, is your request to delete and shift up if the entire row is blank or do you want all blank cells in that range "removed", effectively creating a continuous range within BA103:BE114?, given the header is "Deleting blank rows in range only..."

Alternatively, a before and after example would help clarify