Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number format as condition for not deleting a line
Dear Friends,
I am having a problem with a particular macro I am working on. The following macro is supposed to delete all rows that do not have a number in column A. The user enters the number of rows to process, then lets the macro do its job. Unfortately, I end up with all of the rows being deleted. I have checked my data on the test page and I have two rows that contain numbers in column A. I have verified that these are numbers by right-clicking over these cells and choosing cell-format - both are listed as "number". Here is the code that I have created . . . Counter = InputBox("Enter the total number of rows to process") Worksheets("sheet1").Activate Range("A1").Select For i = 1 To Counter If ActiveCell.NumberFormat = "Number" Then ActiveCell.Offset(1, 0).Select Selection.EntireRow.Delete Counter = Counter - 1 Else Selection.EntireRow.Delete End If Next i Thank you for your help! Mike --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number format as condition for not deleting a line
What is the criteria for determining that a row can be deleted? is it that the
cell for that row in Col A is empty / contains a number / does not contain a number? Can the cell contain things other than a number? Give us the full facts and we'll do what we can for you. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- Seasons Greetings and Very Best wishes to all :-) ---------------------------------------------------------------------------- "mjwillyone" wrote in message ... Dear Friends, I am having a problem with a particular macro I am working on. The following macro is supposed to delete all rows that do not have a number in column A. The user enters the number of rows to process, then lets the macro do its job. Unfortately, I end up with all of the rows being deleted. I have checked my data on the test page and I have two rows that contain numbers in column A. I have verified that these are numbers by right-clicking over these cells and choosing cell-format - both are listed as "number". Here is the code that I have created . . . Counter = InputBox("Enter the total number of rows to process") Worksheets("sheet1").Activate Range("A1").Select For i = 1 To Counter If ActiveCell.NumberFormat = "Number" Then ActiveCell.Offset(1, 0).Select Selection.EntireRow.Delete Counter = Counter - 1 Else Selection.EntireRow.Delete End If Next i Thank you for your help! Mike --- Message posted from http://www.ExcelForum.com/ --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.556 / Virus Database: 348 - Release Date: 26/12/2003 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number format as condition for not deleting a line
How about:
Option Explicit Sub testme02() Dim Counter As Long Dim i As Long Dim delRng As Range Counter = Application.InputBox _ ("Enter the total number of rows to process", Type:=1) If Counter < 1 Then Exit Sub End If With Worksheets("sheet1") For i = 1 To Counter If Application.IsNumber(.Cells(i, 1).Value) = False Then If delRng Is Nothing Then Set delRng = .Cells(i, 1) Else Set delRng = Union(.Cells(i, 1), delRng) End If End If Next i End With If delRng Is Nothing Then 'nothing to do Else delRng.EntireRow.Delete End If End Sub mjwillyone wrote: Dear Friends, I am having a problem with a particular macro I am working on. The following macro is supposed to delete all rows that do not have a number in column A. The user enters the number of rows to process, then lets the macro do its job. Unfortately, I end up with all of the rows being deleted. I have checked my data on the test page and I have two rows that contain numbers in column A. I have verified that these are numbers by right-clicking over these cells and choosing cell-format - both are listed as "number". Here is the code that I have created . . . Counter = InputBox("Enter the total number of rows to process") Worksheets("sheet1").Activate Range("A1").Select For i = 1 To Counter If ActiveCell.NumberFormat = "Number" Then ActiveCell.Offset(1, 0).Select Selection.EntireRow.Delete Counter = Counter - 1 Else Selection.EntireRow.Delete End If Next i Thank you for your help! Mike --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number format as condition for not deleting a line
Gentlemen,
Actually, here is a routine I have that works great to delete all row where the word "Posted" is not found in cell "B" of a row. All needed to do was tweak this so that is would not look at cell "A" of row, determine if the value in cell "A" is a number and delete the ro if it is not. I know that I would need to change the range to "A1" but do not kno what I have to do with the 'If ActiveCell = "Posted" Then' text t get it to look at the numeric cell attribute of the activecell. Worksheets("sheet1").Activate Range("B1").Select Counter = InputBox("Enter the total number of rows to process") For i = 1 To Counter If ActiveCell = "Posted" Then ActiveCell.Offset(1, 0).Select Selection.EntireRow.Delete Counter = Counter - 1 Else Selection.EntireRow.Delete End If Next i Thanks, Mik -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number format as condition for not deleting a line
Gentlemen,
The third row of the reply I just sent should read as follows: would now (instead of not) look at cell "A" of a row, determine if the value in cell "A" is a number and delete Sorry, Mike --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number format as condition for not deleting a line
If application.isnumber(activecell.value) then
would check to see if the activecell were numeric. But your original code actually deletes the row under the cell with posted. And I'd suggest that you start from the bottom and work upwards if you don't want to use the routine I posted. mjwillyone wrote: Gentlemen, Actually, here is a routine I have that works great to delete all rows where the word "Posted" is not found in cell "B" of a row. All I needed to do was tweak this so that is would not look at cell "A" of a row, determine if the value in cell "A" is a number and delete the row if it is not. I know that I would need to change the range to "A1" but do not know what I have to do with the 'If ActiveCell = "Posted" Then' text to get it to look at the numeric cell attribute of the activecell. Worksheets("sheet1").Activate Range("B1").Select Counter = InputBox("Enter the total number of rows to process") For i = 1 To Counter If ActiveCell = "Posted" Then ActiveCell.Offset(1, 0).Select Selection.EntireRow.Delete Counter = Counter - 1 Else Selection.EntireRow.Delete End If Next i Thanks, Mike --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number format as condition for not deleting a line
Dave,
Thank you very much! Your single-line code worked great! Happy New Year, Mike --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deleting rows in a worksheet if condition is met | Excel Discussion (Misc queries) | |||
Line Break in Cell Number Format | Excel Discussion (Misc queries) | |||
deleting condition after the first duplicate | Excel Worksheet Functions | |||
how 2 Count number of cells that have specific condition format? | Excel Worksheet Functions | |||
Deleting conditional formats based on condition | Excel Programming |