This seems to work as long as you don't have FALSE in two or more adjacent rows (data changed again on next row). Two FALSE in two rows will yield two blank rows above the first FALSE. Three FALSE in three rows will yield three blank rows above the first FALSE. Maybe I missed something?
No, you are correct. This process didn't account for that scenario. I will need to work on a different process altogether to deal with that issue, Will probably result in a new video.
I did find this VBA code that works in this situation. It assumes you have headers in row 1, your data starts in row 2, and column B is where the changes occur that you want to check: Sub InsertRowsAtValueChangeColumnB() Dim X As Long, LastRow As Long Const DataCol As String = "B" Const StartRow = 2 LastRow = Cells(Rows.Count, DataCol).End(xlUp).Row Application.ScreenUpdating = False For X = LastRow To StartRow + 1 Step -1 If Cells(X, DataCol).Value Cells(X - 1, DataCol) Then Rows(X).Insert Next Application.ScreenUpdating = True End Sub
Can you be more specific? If you want you can send me an e-mail with more details and/or a sample of what you want to do. Send to mrempel@excel-bytes.com
What about if there are multiple false in a row? That didn't seem to work for me when there are multiple false the other rows were perfect except when it came to multiple repeated false in a row
Hello thanks for the video. - we have been trying to do this. But when we try to highlight our column, the insert box (e.g to select entire rows etc) does not show up (and the ctrl + formula doesn't work), so we can't select this option. The only thing that happens is an automatic insert of another column next to it. Any ideas? Thanks
Millie, I'm not sure what the issue may be, could be a couple of things. If you want, I'd be happy to do a phone or video call to see what the issue is. If you want to pursue this, please send me an e-mail at mrempel@excel-bytes.com and we can continue there.
Great video very easy. I had a problem when I had cells change one after the other. So there were three falses in a row which is correct but when I went to insert it inserted three rows after the three falses. So they didnt separate. Any ideas?
sir, I am not getting solution for those cells having only one data and no other same value. For example, only one cell contains ed then it may not give the same result.
Whenever you double click on the lower right corner of a cell (cursor turns into a solid plus sign) that has anything in it, Excel will automatically copy it down as far as any entries in the column immediately to the left or right. If there is nothing in a column to the left or right, nothing will happen.
The theory would be the same, but I need to be more clear on how your quantity number changes. Can you provide an example file? Send to mrempel@excel-bytes.com
Can you please explain further what you want? Possibly send me a sample file with what you want to do with an explanation. Send to mrempel@excel-bytes.com
Can you please explain your question a bit better? "F" is not a row but a column. Do you want to add up the entire column "F" or insert subtotals in column "F" at each blank row? Or is it something else?
Hello sir, my one query is how to many name a column and repeated some 4,5,3,7,2 time how to coloured at once in excel then sort by colour show grouping but every group show different different colour. Please help😂
Thanks for the video, its still helped this Excel newb in 2023!
Saved me at least 2 hours of work today, and countless more in the future, thank you!
Great! Glad you found it useful!
Excellent explaining
Thanks, glad your liked it.
Este video debería tener 1 millon de Likes
muchas gracias
Thank you!!!!!!! I have been going nuts trying to figure this out! Thank you, thank you, THANK YOU!!!
Glad you liked it!
Thank you! A google search linked me to you video and it was exactly what I was wanting to do.
Great, glad you found it useful!
Thank you ..you saved me an hour.....
Great! Glad you found it useful.
Thanks man!
You're Genius.
Thanks. Glad you found it useful.
for long i've been trying to figure this out, thanks !
Glad you found it useful!
Thanks, it worked perfectly!
Thanks, glad you liked it!
THANKS SO MUCH
Glad you liked it.
Thank you
This is help me alot
Great to hear!
Genius!
Thanks!
Thanks for this tip. Very simply and effective.
Glad you liked it!
Very useful technique. I hadn't thought of using the find all feature to do stuff like this before 👍
Thanks, glad you liked it
Thank you. It was great help
Glad it was useful for you.
This was fantastic!! Thank you for sharing!!
I'm glad you liked it!
You really helped me, you are a legend... Thanks
Thanks, I'm glad you found this useful!
Thank you so much Micheal. Actually i was searching for same thing from last few days. all the best wishes for you channel.👍👍👍
I'm glad you found this useful!
Thank you Michael! Very useful Technique! Huge Time Saver
Thanks! Glad your liked it.
Thank you!
You saved my 45 minutes
Glad I could help!
Life saver
It worked for me! CTRL + wouldn't work for me though. So what I did was click the insert tab and select 'Add Sheet Rows' and it worked. :) Thanks.
‘+’ is shift +, so you need ctrl shift +
thanks :) @@mandypdx
Thanks so much!
I'm glad you found this useful!
This seems to work as long as you don't have FALSE in two or more adjacent rows (data changed again on next row). Two FALSE in two rows will yield two blank rows above the first FALSE. Three FALSE in three rows will yield three blank rows above the first FALSE. Maybe I missed something?
No, you are correct. This process didn't account for that scenario. I will need to work on a different process altogether to deal with that issue, Will probably result in a new video.
I did find this VBA code that works in this situation. It assumes you have headers in row 1, your data starts in row 2, and column B is where the changes occur that you want to check:
Sub InsertRowsAtValueChangeColumnB()
Dim X As Long, LastRow As Long
Const DataCol As String = "B"
Const StartRow = 2
LastRow = Cells(Rows.Count, DataCol).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To StartRow + 1 Step -1
If Cells(X, DataCol).Value Cells(X - 1, DataCol) Then Rows(X).Insert
Next
Application.ScreenUpdating = True
End Sub
@@ExcelBytes Hi! Is there any new solution for the case Joel described? Thanks!
@@evgueniteplits5687 See this tutorial: www.excel-bytes.com/inserting-a-blank-row-at-a-column-change-update-in-excel/
I am so glad it wasn't just me. I thought I was for sure doing it wrong (repeatedly)
Love You sir, You solved my problem
Thanks! Glad your liked it and it was useful for you.
very useful but how will i make an empty row automaitcally show a value at the end of the row like an interest
Can you be more specific? If you want you can send me an e-mail with more details and/or a sample of what you want to do. Send to mrempel@excel-bytes.com
For us that use this maybe once or twice a month. How do we do it without shortcuts? I know it takes longer just can’t remember the combos
I used 4 keyboard shortcuts in this tutorial:
Ctrl F: Home>Find&Select>Find
Ctrl C: Home>Copy
Ctrl +: Home>Insert>Insert Sheet Rows
Ctrl -: Home>Delete>Delete Sheet Columns
What about if there are multiple false in a row?
That didn't seem to work for me when there are multiple false the other rows were perfect except when it came to multiple repeated false in a row
Try this tutorial: ruclips.net/video/qWbaGnZlLn8/видео.html
Hello how you added heading of the cell after =..............like =[@salespeople]
how we can put sum in all the added rows for above number of columns
Your probably better off using the SUBTOTAL function than this process.
Hello thanks for the video. - we have been trying to do this. But when we try to highlight our column, the insert box (e.g to select entire rows etc) does not show up (and the ctrl + formula doesn't work), so we can't select this option. The only thing that happens is an automatic insert of another column next to it. Any ideas? Thanks
Millie, I'm not sure what the issue may be, could be a couple of things. If you want, I'd be happy to do a phone or video call to see what the issue is. If you want to pursue this, please send me an e-mail at mrempel@excel-bytes.com and we can continue there.
if we have only one row for one unique number , its now working
Try this one: ruclips.net/video/qWbaGnZlLn8/видео.html
Great video very easy. I had a problem when I had cells change one after the other. So there were three falses in a row which is correct but when I went to insert it inserted three rows after the three falses. So they didnt separate. Any ideas?
This video addresses that issue: ruclips.net/video/qWbaGnZlLn8/видео.html
@@ExcelBytes Thank you!!
but what will happened when "salespeople" column has single row data not multiple row data??? please reply.
Check this one out: ruclips.net/video/qWbaGnZlLn8/видео.html
ctrl + + is not working for me
You can just right click, then click on "insert" and choose entire row and click OK
sir, I am not getting solution for those cells having only one data and no other same value. For example, only one cell contains ed then it may not give the same result.
I did a follow up that addressed this issue: www.excel-bytes.com/inserting-a-blank-row-at-a-column-change-update-in-excel/
What's the shortcut command to copy down the formula you use at 3:40?
Whenever you double click on the lower right corner of a cell (cursor turns into a solid plus sign) that has anything in it, Excel will automatically copy it down as far as any entries in the column immediately to the left or right. If there is nothing in a column to the left or right, nothing will happen.
Hello, sir how can I add blank lines between data by different quantity number wise
The theory would be the same, but I need to be more clear on how your quantity number changes. Can you provide an example file? Send to mrempel@excel-bytes.com
Sir problem solved but i want serial number also changed after blank rows (always start 1 after blank rows) plz solved my problem
Can you please explain further what you want? Possibly send me a sample file with what you want to do with an explanation. Send to mrempel@excel-bytes.com
How to add the total price in the space of ROW "F"
Can you please explain your question a bit better? "F" is not a row but a column. Do you want to add up the entire column "F" or insert subtotals in column "F" at each blank row? Or is it something else?
Excellent, but still not scrolling in to formulas! Especially for us old folk.
What do you mean "scrolling in to formulas"?
Closeup. Panning in.
Hello sir, my one query is how to many name a column and repeated some 4,5,3,7,2 time how to coloured at once in excel then sort by colour show grouping but every group show different different colour. Please help😂
Can you send me a file showing more detail of exactly what you want and I'll see if I can help. Send to mrempel@excel-bytes.com
Excel Bytes sir, I m send the sample file ur email address. please help 😂 file subject name of grouping color
This method FAILS to work when there are 2 consecutive different entries entries in a column.
That's why I created this tutorial: ruclips.net/video/qWbaGnZlLn8/видео.html