Emptying cells after blank rows

Spencery

New Member
Joined
Sep 16, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello, I've been looking for a way to empty cells that appear after blank rows. I believe VBA will be needed but I have very little experience in it. Let me show you the example of what I mean:

1631781742596.png


So I need to clear the cells colored in yellow. I don't want to delete the rows, only to empty the cells. Is it even possible? :) I would really appreciate all the help. Thank you.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,725
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

Assuming there is always at least one name at the top of the list, try this with a copy of your workbook.

VBA Code:
Sub Clear_Data()
  ActiveSheet.UsedRange.Offset(Range("A1").End(xlDown).Row).ClearContents
End Sub
 

Spencery

New Member
Joined
Sep 16, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Welcome to the MrExcel board!

Assuming there is always at least one name at the top of the list, try this with a copy of your workbook.

VBA Code:
Sub Clear_Data()
  ActiveSheet.UsedRange.Offset(Range("A1").End(xlDown).Row).ClearContents
End Sub

Thank you, it kind of works but not quite. It deletes the entire rows so in this case I am left with something like that

1631792751346.png


So I lost the rest of the positions from the second table. Could we use this code to clear for example three cells within the row in the table one and the same thing with table two except this time the whole process begins three rows lower? Or there have to be created two separeted macros for such an effect?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,725
Office Version
  1. 365
Platform
  1. Windows
Sorry, I didn't study your example well enough and thought they were just two separate samples. :oops:

Try this instead

VBA Code:
Sub Clear_Data_v2()
  Dim rA As Range
 
  For Each rA In Rows(1).SpecialCells(xlConstants).Areas
    With rA.CurrentRegion
      .Offset(.Rows.Count).Resize(Cells(Rows.Count, rA.Column).End(xlUp).Row).ClearContents
    End With
  Next rA
End Sub
 
Solution

Forum statistics

Threads
1,147,635
Messages
5,742,248
Members
423,717
Latest member
rubthenut

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top