Is there any simple way to insert blank rows between two rows with different numbers!
Eg:
column a:
23
34
37
56
i want to insert blank rows between 23 and 34 rows which is around 10 blank rows , and again 2 blank rows between 34 and 37 and so on ..
Another situation is sometime i have two same value
column a:
23
34
34
37
56
In this, i dont want any blank rows between 34 and 34
How this can be done !
Many thanks in advance !
Last edited by mike4uuu; 05-26-2011 at 04:05 AM.
Try:
Please Login or Register to view this content.
Regards
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Wheni try to run the above code , its giving an error
Run time error 1004
Application define or object define error
AND WHEN I DEBUG
i can c a yellow line
Range("A" & i + 1).Resize(Range("A" & i + 1).Value - Range("A" & i).Value - 1, 1).EntireRow.Insert
Please post the sample workbook you are using to test the code
Thanks
You will get that error if the numbers are not in ascending order as the code will try to insert a negative number of rows ... and it's not going to be happy about doing that.
Regards
A b c d
6445 23 00:00.0 0
6467 23 00:00.0 0
6469 23 00:00.0 0
6470 23 00:00.0 0
6470 23 00:00.0 0
6472 23 00:00.0 0
6482 23 00:00.0 0
When i run it on a single column with the following data , its not working' properly
A
6445
6467
6469
6470
6470
6472
6482
OK, try this:
Please Login or Register to view this content.
Regards
It is inserting but not in the right way ..can somebody please solve this problem ...
Last edited by mike4uuu; 05-26-2011 at 01:42 AM.
"It is inserting but not in the right way ..can please solve this problem ... "
And "not in the right way" means what, exactly?
You need to describe what it actually does and what you want it to do so the difference is clear.
Alternatively, you could have a go at fixing it yourself ;-)
This is what i m getting !
You can see the screenshot in this link
\1
@TMShucks I'm not very good with excel ... Thanks a lot for your prompt reply , i have uploaded the screenshot too !
Will be very glad if you can help me with this issue !
Please upload the workbook itself, with the data and code in it, not just a picture of it. I can't do anything with that.
Regards
i have uploaded the sample file on which i was testing !
the code i m using is same as provided by you
PHP Code:Option Explicit
Sub InsertRowsV2()
Dim lLastRow As Long
Dim i As Long
Application.ScreenUpdating = False
lLastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = lLastRow - 1 To 2 Step -1
If Range("A" & i + 1).Value - Range("A" & i).Value > 0 Then
Range("A" & i + 1).Resize(Range("A" & i + 1).Value - Range("A" & i).Value, 1).EntireRow.Insert
End If
Next 'i
Application.ScreenUpdating = True
End Sub
OK, the problem is that I expected there would be a header row so the code stops at row 2
Change this:
Please Login or Register to view this content.
To this:
Please Login or Register to view this content.
Full code:
Please Login or Register to view this content.
This is why it is always better to provide a sample workbook and a description of the problem and what you want to achieve at the outset ;-)
Regards
it is still inserting extra rows , for example for a column
1
2
4
5
5
8
its inserting rows between
1 and 2 ans two rows between 2 and 4 and one row between 4 and 5 and again 3 rows between 5 and 8
1
blank row
2
blank row
blank row
4
blank row
5
5
blank row
blank row
blank row
8
while the actual result should be
1
2
blank row
4
5
5
blank row
blank row
8
OK, a couple of ways you can do that:
Please Login or Register to view this content.
Please Login or Register to view this content.
Note that this code is for the scenario where you just have a column of numbers and you do *not* have a header ... as per your sample.
Regards
Thanks Buddy ! This works perfectly!
Thanks a Ton!
Who ever u r - God Bless u my Friend ! And i will definitely come back , promote this forum in what ever way i can !
Thanks once again!
You're welcome. Kind of a relief, at last ;-) Thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks