• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macros

N

niting

New Member
Hi forum,


I had read sometime bak in the forum, the macro for deleting alternate rows. I tried searching for it, but could not find it.


Could u share the Macro and could we also apply the macro for particular ranges instead of the whole sheet???


Thanks

Nitin
 
Niting

Give this a go

[pre]
Code: 
Sub DeleteRows()
Dim i As Long
Dim myrange As Range
Set myrange = ActiveCell.CurrentRegion
For i = myrange.Rows.Count To 1 Step -1
If myrange(i, 1).Row Mod 2 = 0 Then ' Change to 1 for odd rows
myrange(i, 1).EntireRow.Delete
End If
Next
End Sub[/pre]
To use select any cell

The code will expand to all joined cells in the current area

and then delete all the Even Rows

To delete the odd rows change the line

If myrange(i, 1).Row Mod 2 = 0 Then ' Change to 1 for odd rows
 
HUI,


Forgive me for coming again on this post!!


I was asked by a friend for the problem and I gave him the solution proposed by you. I did not have the need for it and for that matter have not used macro till date.


Hui, now I am in soup. I have few sheets coming strait from the SAP which has every alternate rows which are blank starting from row 10.


Now I have to delte these alternate rows. HUI, please could you care to tell me how do I use the macro proposed by you in the solution to acheive the result. I tried using the macro,although with no success since I don't know how to use it.


Thanks and sorry for inconvenience.
 
In Hui's macro, take a look at this line:

Code: 
For i = myrange.Rows.Count To 1 Step -1



This tells the macro to look at every row from the end of workbook to 1, counting backwords. If you want to start (aka, end) at row 10, change the line to:



For i = myrange.Rows.Count To 10 Step -1
 
Luke,


Hie!!


Thanks for your reply. Luke, what ever I have learned in excel is courtsey this site. However, I have never ever used Macros. I go to Macro tab under the developer tab and when I type the macro name, the create tab gets active. Then it shows the screen with start sub and end sub text.


What I assumed was that I would paste the macro written above in the field, delete the original start and end sub text and save it. And then I would go to the worksheet and run the macro. However, it does not yield the desired result. It just deltes random rows. Not the entire alternate rows in thw worksheet.


I hope I have been able to clearly share my problem. Could you please help now considering my "rich" experience with using the macros.


Thanks in advance

Niting
 
Luke,


Similar to the query above,I also have a situation wherein I have to retain rows if there is a value/text in a particular column. If the column entry is blank, I would like the row to be deleted.


Thanks in advance

Niting
 
Niting


On the Developer Tab, Click Visual Basic

Select the Sheet of the Workbook you want to apply the code to

and past the code in the Upper pane on the right


Modify the code as per Lukes comments
 
HUI,


i am sorry. But just not able to get the result. You mentioned to paste the code in the upper pane on the right. There was just a blank dark space and nothing could be pasted therein. So, I explored the contents and found that under the VB, Insert command, Module is there. I used that and pasted it there.


Then I return to the worksheet and click on any cell and run the macro under the developer tab "Macro". However, it just deleted one row of the worksheet.


I know being a pain, but pls be patient.


Thanks

Niting
 
Hui,


The worksheet looks like dis-: (Sorry tried to upload the file, but was not able to)


A B C D E F G


Row 1 Class Desc APC BusA APC Fy DEp FY Acq

Row 2

ROw 3 2000 Build 110220 BP01 720 500 100

Row 4

Row 5 2000 Build 110220 BP01 720 500 100

Row 6

ROw 7 4100 P & m 110040 BP01 3510 2000 500

ROw 8

Row 9 4100 P & m 110040 BP01 3510 2000 500


Hui, As you can see above, the Alternate rows are blank. I want to delete the alternate rows as the sheet which is coming from SAP has quite a number of rows and manually deleting the rows would be a big task.


Thanks in advance

Niting
 
Try:

[pre]
Code: 
Sub Delete_Blank_Rows()
Dim i As Long
Dim myrange As Range
Set myrange = Range("A10:a1")
For i = myrange.Rows.Count To 1 Step -1
If myrange(i, 1).Value = "" Then myrange(i, 1).EntireRow.Delete
Next
End Sub
[/pre]
 
You must log in or register to reply here.
Back
Top