Code needs modifiying to help when deleting rows

John T

Board Regular
Joined
Nov 28, 2013
Messages
145
Office Version
  1. 365
Platform
  1. Windows
Hi, I currently have the below code that deletes rows between row 220 - 40 if the data in column A for that row is blank.
Would it be possible to then rather than delete all rows, leave 10 blank rows after the last row in column A with data.
for example if row 40 were my only row then the 10 rows after that would be inserted.

The other option is to start at A1000 x1up and ofset it by -3 then insert 10 rows but i don't know which is the easiest way to go or how to add that to my existing code.

Thanks

Excel Formula:
Sub Del()

For r = 220 To 40 Step -1
Set Rng = Range("A" & r & ":i" & r)
If WorksheetFunction.CountA(Rng) = 0 Then
Rng.Resize(, 11).Delete Shift:=xlUp
End If
Next r

End Sub
 
What happens if you put this line of code at the end of your initial code?

VBA Code:
    Range("A" & Range("J" & Rows.Count).End(xlUp).Row).EntireRow.Offset(1).Resize(10).Insert Shift:=xlDown
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
That seems to have worked. Thanks.
The only issue is that the formula in column J hasn't copied down in the 10 inserted rows.
 
Upvote 0
That seems to have worked. Thanks.
The only issue is that the formula in column J hasn't copied down in the 10 inserted rows.
Which Post# is working for you...
 
Upvote 0
Your one igold. Thanks. I've not had a chance to test the other one yet.
Still got the issue with the formula in column J though.
 
Upvote 0
What happens if you put this line of code at the end of your initial code?

VBA Code:
    Range("A" & Range("J" & Rows.Count).End(xlUp).Row).EntireRow.Offset(1).Resize(10).Insert Shift:=xlDown
This one didnt work. It made it look like the last screenshot i posted.
Thanks for your suggestion.
 
Upvote 0
I don't know how. Maybe it's my sheet as the same thing happened with some other suggestions.
It ends up looking like this.

1658263940594.png
 
Upvote 0
Does this fill your formula...

VBA Code:
Sub Del()

    Dim rng As Range
    Dim lRow As Long
    
    Set rng = Range("A40:A220")
    rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    lRow = Range("J40").End(xlDown).Row + 1
    Range("J" & lRow & ":J" & lRow + 10).EntireRow.Insert
    Range("J40").AutoFill Destination:=Range("J40:J" & lRow + 10)

End Sub
 
Upvote 0
Solution
You're welcome. I was happy to help as I am sure the other members were as well. It took a little back and forth, but we got it down.

Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

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
Back
Top