• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 619
  • Last Modified:

Insert blank row after data change

I have a spreadsheet that I do frequent sorting on it, however, I would like to be able to automatically insert a blank row after a specific data change (say column E0. Then the next time when I sort by another criteria, I can then insert blank rows again (this time after data change in column D).

Thank you in advance.
0
Farah Mohammed
Asked:
Farah Mohammed
  • 3
  • 2
1 Solution
 
nutschCommented:
That's not very clear to me, can you precise or paraphrase?

Thomas
0
 
Farah MohammedBuyerAuthor Commented:
I have the following spreadsheet

Part #      Qty
Box 1      10
Box 1      20
Box 2     10
Box 2     50

I need it to add a line after each different Part #. Unfortunately, we are talking about 1000 lines, so if there is a way for me to do it (not manually), it would be great.
0
 
nutschCommented:
try this, assuming your part #s are already sorted

Thomas

sub InsertRows()
dim lstRow as long, i as long


lstRow=range("A" & rows.count).end(xlup).row

for i=lstrow to 3 step -1

if cells(i,"A")<>cells(i-1,"A") then _
rows(i).insert

next i

end sub

Open in new window

0
 
Farah MohammedBuyerAuthor Commented:
I guess, macro is the way to go.

Thank you
0
 
nutschCommented:
THanks for the grade.

If you don't want a macro, I'd put the following formula in a new columns starting in row 3
=A3<>A2
copy it all the way down, do a filter on the true value, select the visible values
Do Alt+;
then Alt + I + R
to insert rows about the selected values.

Thomas

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now