Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows
Hello,
From cell A1 to A3500, I have many cells with infos in it and many cells with no info at all. How can I create a macro that will delete the entire row in which colomn A has no info??? In example: A1: Test 1 A2: A3: A4: A5: Test 2 A6: A7: Test 3 A8: I would like to create a macro that will automatically delete rows 2-3-4-6-8 and leave intact rows 1-5-7. Thanks!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows
Try this -- it works well for me:
Private Sub DeleteBlankRows() Dim lastrow As Long Dim r As Long lastrow = Range("C" & Rows.Count).End(xlUp).Row For r = lastrow To 2 Step -1 If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then ActiveSheet.Rows(r).Delete End If Next End Sub HTH "One-Leg" wrote in message ... Hello, From cell A1 to A3500, I have many cells with infos in it and many cells with no info at all. How can I create a macro that will delete the entire row in which colomn A has no info??? In example: A1: Test 1 A2: A3: A4: A5: Test 2 A6: A7: Test 3 A8: I would like to create a macro that will automatically delete rows 2-3-4-6-8 and leave intact rows 1-5-7. Thanks!!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows
Sub DeleteRowsWithBlanks()
Range("A:A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete End Sub HTH, Bernie MS Excel MVP "One-Leg" wrote in message ... Hello, From cell A1 to A3500, I have many cells with infos in it and many cells with no info at all. How can I create a macro that will delete the entire row in which colomn A has no info??? In example: A1: Test 1 A2: A3: A4: A5: Test 2 A6: A7: Test 3 A8: I would like to create a macro that will automatically delete rows 2-3-4-6-8 and leave intact rows 1-5-7. Thanks!!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows
Your "modified code" will look like this:
Private Sub DeleteBlankRows() Dim lastrow As Long Dim r As Long lastrow = Range("A" & Rows.Count).End(xlUp).Row For r = lastrow To 2 Step -1 If Application.CountIf(Cells(r, "A").Resize(1, 1), "") = 1 Then ActiveSheet.Rows(r).Delete End If Next End Sub :) "One-Leg" wrote in message ... Hello, From cell A1 to A3500, I have many cells with infos in it and many cells with no info at all. How can I create a macro that will delete the entire row in which colomn A has no info??? In example: A1: Test 1 A2: A3: A4: A5: Test 2 A6: A7: Test 3 A8: I would like to create a macro that will automatically delete rows 2-3-4-6-8 and leave intact rows 1-5-7. Thanks!!! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows
Bernie,
I like your code better -- quick and to-the-point! ;) "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Sub DeleteRowsWithBlanks() Range("A:A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete End Sub HTH, Bernie MS Excel MVP "One-Leg" wrote in message ... Hello, From cell A1 to A3500, I have many cells with infos in it and many cells with no info at all. How can I create a macro that will delete the entire row in which colomn A has no info??? In example: A1: Test 1 A2: A3: A4: A5: Test 2 A6: A7: Test 3 A8: I would like to create a macro that will automatically delete rows 2-3-4-6-8 and leave intact rows 1-5-7. Thanks!!! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows
You have been given a choice of macros, but could do the job manually.
Select column A and F5SpecialBlanksOK EditDeleteEntire row. Gord Dibben MS Excel MVP On Fri, 8 Dec 2006 08:41:00 -0800, One-Leg wrote: Hello, From cell A1 to A3500, I have many cells with infos in it and many cells with no info at all. How can I create a macro that will delete the entire row in which colomn A has no info??? In example: A1: Test 1 A2: A3: A4: A5: Test 2 A6: A7: Test 3 A8: I would like to create a macro that will automatically delete rows 2-3-4-6-8 and leave intact rows 1-5-7. Thanks!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
easy way to delete all rows with no text in them? | New Users to Excel | |||
delete rows | Excel Discussion (Misc queries) | |||
Delete Rows where cells does not meet criteria | Excel Worksheet Functions | |||
Want to delete rows | Excel Discussion (Misc queries) | |||
How can we delete rows permanently from excel sheet | Excel Discussion (Misc queries) |