Blank rows deletion

[Closed]
Report
-
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
-
Hello,

Dear friends,

I really appreciate your support and help all the time.

I have another problem and I need your help once again. I have files with name Jan 2010, Feb 2010............Dec 2010 (12 files). I am using a macro which search rows contain specific word according to my desire and copy searched rows in another worksheet "Records".

Searched rows from Jan 2010 starts copying from row # 1 in Worksheet "Records" the searched rows from Feb 2010 starts copying from row # 1000 in worksheet "Records" and so on. The searched rows copied in worksheet "Records" with gape of 1000 rows. I need a macro which work on worksheet "Records" leave one blank row between the data / rows and delete all other blank rows between data of months. Just to show separation of data of each month.

e.g. Jan's data then one blank row then Feb's data then one blank row then Mar's data then a blank row and so on.

I will be very grateful if someone can help me.

Thank you,

Aslam



6 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
copy the data in sheet 1 in some other location e.g. sheet 2 also as precaution


now try this macro on sheet1

Sub test()
Dim r As Range, j As Long, r1 As Range
j = Cells(Rows.Count, "A").End(xlUp).Row
Set r = Cells(j, 1)
Do
Set r1 = r.End(xlUp)
Range(r.End(xlUp).Offset(-1, 0), r1.End(xlUp).Offset(2, 0)).EntireRow.Delete
Set r = r1.End(xlUp)
If r.Row = Range("A1").End(xlDown).Row Then Exit Do
Loop
End Sub
Dear venkat1926,

Thank you very much for the solving this problem. The macro you have write its working. Just need one amendment in this, that the macro will work only on sheet1 of workbook "Records" not on all open workbooks.
Dear venkat1926,

When I run this macro first time I just noticed that one blank line is there between record of each months data and all other blank lines deleted. but after it I noticed that its deleting last row of the months which contain data. please check it and make sure that it will not delete any row which contain data.

Thank you
Dear Venkat1926,

I am very sorry that I have write above message to you in hustle. Actually the macro is deleting first two rows only all other working is good.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
quote
ust need one amendment in this, that the macro will work only on sheet1 of workbook "Records" not on all open workbooks.
unquote


First save the workbook safely some where to recover if something goes wrong.
see this macro

Sub test() 
Dim r As Range, j As Long, r1 As Range, k As Long 
For k = 1 To Worksheets.Count 
With Worksheets(k) 
j = .Cells(Rows.Count, "A").End(xlUp).Row 
Set r = .Cells(j, 1) 
Do 
Set r1 = r.End(xlUp) 
Range(r.End(xlUp).Offset(-1, 0), r1.End(xlUp).Offset(2, 0)).EntireRow.Delete 
Set r = r1.End(xlUp) 
If r.Row = .Range("A1").End(xlDown).Row Then Exit Do 
Loop 
End With 
Next k 
End Sub 
Dear venkat1926,

Thank you for your wonderful efforts to solve my problem.

I have tested this macro and its giving error on following string:

Set r = r1.End(xlUp)

could you please remove this error.

I am really thankful to you for your help.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
can you please post a very small extract of atleast 2 data sheets
Dear venkat1926,

I cant send you the extracted sheets through this website because there is no option to attache the file. So, kindly let me know your email address so I can forward you the sheets.

Thank you,

Aslam
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
488
Hi,

To send an extracted sheets you can upload your file on https://authentification.site and copy/paste the created link into your next message.

Regards
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
take aquarelle'S suggestion
I have uploaded the extracted files Data 1 and Data 2 on http://www.speedyshare.com/ the links are here as under

http://speedy.sh/k2xYv/Data-1.xlsx
http://speedy.sh/NJ5CD/Data-2.xlsx

Now, please amend the macro accordingly.

Hope to receive your prompt reply

Thank you,

Aslam
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
thank you for uploading the two files. in your first message you talk about worksheet named "records". there is no such sheet in either of your two files

I suggest re frame your questions with respect to these two files.
Dear Venkat1926,

I need one blank row after record of each month rest should be deleted, for this purpose I am using following macro but its not working properly:

Public Sub DeleteRow()

' DeleteRow Macro
'
' Keyboard Shortcut: Ctrl+j

Dim x As Long
Dim y As Long
x = Range("C65536").End(xlUp).Row

For y = x To 2 Step -1

If Cells(y, 2).Value = "" And Cells(y, 3).Value = "" And Cells(y, 4).Value = "" Then

Rows(y).Delete

End If

Next y
End Sub

kindly check it and amend it where its needed.

just keep one row blank row after records of each month to make it seperate, rest blank rows should be deleted through macro. The macro should work till last row of the worksheet.

Thank you,

Aslam
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
Aslam there appears to be a SPAM reply. inform administrator

however your request
"just keep one row blank row after records of each month to make it seperate, rest blank rows should be deleted through macro. The macro should work till last row of the worksheet. "

try this macro

Public Sub DeleteRow()

' DeleteRow Macro
'
' Keyboard Shortcut: Ctrl+j

Dim x As Long
Dim y As Long
x = Range("C65536").End(xlUp).Row

For y = x To 2 Step -1

If Cells(y, 2).Value = "" And Cells(y, 3).Value = "" And Cells(y, 4).Value = "" Then

Rows(y).Delete

End If

Next y
For y = 2 To x
If Cells(y, "B") <> "" Then Rows(y + 1).Insert
Next y


End Sub
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
488
Hi,

Aslam there appears to be a SPAM reply. inform administrator
The spam message has been deleted.
Thus when you see a spam message or a message out of charter, use the "Report" link near the message to indicate it to us.

Thanks by advance :)