Non number cells

I would like to create a macro that will remove an entire 
row if in the A colum I have anything else but numbers 
(text for example).
0
anonymous (74722)
4/7/2004 7:33:51 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
457 Views

Similar Articles

[PageSpeed] 6

I would like to create a macro that will remove an entire 
row if in the A colum I have anything else but numbers.  
When I say anything else than numbers, I even mean if a 
cell is empty, I want to delete the entire row.

The reason is that some rows have nothing in colum "A" 
but have other things in other colums.  I still want 
those colums deleted...

Thanks!!!
0
anonymous (74722)
4/7/2004 7:46:01 PM
Hi Ben

You can use this that will only keep the rows with a numeric value
in the A column

Sub Example1()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    Firstrow = ActiveSheet.UsedRange.Cells(1).Row
    Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
    With ActiveSheet
        .DisplayPageBreaks = False
        For Lrow = Lastrow To Firstrow Step -1
            If IsError(.Cells(Lrow, "A").Value) Then
                'Do nothing, This avoid a error if there is a error in the cell

            ElseIf Not IsNumeric(.Cells(Lrow, "A").Value) Then .Rows(Lrow).Delete
            End If
        Next
    End With
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With
End Sub



-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"Ben..." <anonymous@discussions.microsoft.com> wrote in message news:19afb01c41cd8$f4d6d490$a401280a@phx.gbl...
> I would like to create a macro that will remove an entire
> row if in the A colum I have anything else but numbers.
> When I say anything else than numbers, I even mean if a
> cell is empty, I want to delete the entire row.
>
> The reason is that some rows have nothing in colum "A"
> but have other things in other colums.  I still want
> those colums deleted...
>
> Thanks!!!


0
rondebruin (3790)
4/7/2004 8:02:24 PM
Hi Ben I forgot the empty cells

Use this

Sub Example1()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    Firstrow = ActiveSheet.UsedRange.Cells(1).Row
    Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
    With ActiveSheet
        .DisplayPageBreaks = False
        For Lrow = Lastrow To Firstrow Step -1
            If IsError(.Cells(Lrow, "A").Value) Then
                'Do nothing, This avoid a error if there is a error in the cell

            ElseIf Not IsNumeric(.Cells(Lrow, "A").Value) Or _
            IsEmpty(.Cells(Lrow, "A").Value) Then .Rows(Lrow).Delete
            End If
        Next
    End With
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With
End Sub


-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"Ben..." <anonymous@discussions.microsoft.com> wrote in message news:19afb01c41cd8$f4d6d490$a401280a@phx.gbl...
> I would like to create a macro that will remove an entire
> row if in the A colum I have anything else but numbers.
> When I say anything else than numbers, I even mean if a
> cell is empty, I want to delete the entire row.
>
> The reason is that some rows have nothing in colum "A"
> but have other things in other colums.  I still want
> those colums deleted...
>
> Thanks!!!


0
rondebruin (3790)
4/7/2004 8:05:28 PM
HI,

Here's a macro that I believe Frank Kabel posted
a day or so ago - deletes if .value = 0 - modify
for your own situation (.value = "" ???).
(be sure to make a copy of your data first)

jeff



Sub delete_rows()
' delete all rows if they have a zero in col A
Dim RowNdx As Long
Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For RowNdx = LastRow To 1 Step -1
        with Cells(RowNdx, "A")
            if .value = 0 then
                Rows(RowNdx).Delete
            End If
        end with
Next RowNdx
Application.ScreenUpdating = True
End Sub
>-----Original Message-----
>I would like to create a macro that will remove an 
entire 
>row if in the A colum I have anything else but numbers.  
>When I say anything else than numbers, I even mean if a 
>cell is empty, I want to delete the entire row.
>
>The reason is that some rows have nothing in colum "A" 
>but have other things in other colums.  I still want 
>those colums deleted...
>
>Thanks!!!
>.
>
0
anonymous (74722)
4/7/2004 8:12:52 PM
Reply:

Similar Artilces:

Report showing ID numbers only
I have tables set up, linked properly, forms, etc. Since I did forms and subforms, I had to create separate tables and link them, so I used a primary key ID in the tables. So far my database shows it is working - when information is entered into the form, it shows up in the correct tables and all that, but since I used all these primary keys, the tables just have a bunch of the numbers that pertain to what was input rather than actual words. I created a query with the fields that I want shown in the report, and I created the report and when I run it it shows up the correct fields I want...

update the same cell into another column forming a list on opening
hi there, i have made a spreadsheet to enter data and then view the resulting statistics that are generated, i have one cell that changes in value after entering the data, and i would like to keep a record of what values this cell has been, i was after a formula that would update that single cell's value into a column forming a list each time the spreadsheet was opened or the value in the cell changed, like a field perhaps, this would fill up the column with a list of results from which to generate more stats and a graph etc. any help would be great, thank you -- thank...

Excel 2003 random number generator
Hi, I'm using Excel 2003 to do some actuarial work. I notice that if a very large number of random number is generated(say 10000), and the spreadsheet is run by Macro for a large number of times(say 1000), sometimes, Excel 2003 will generate some negative random numbers, which is nonsense. Some of my friends told me earlier versions of Excel didn't have that problem. I'm sort of wondering what is going on with Excel 2003 random number generator and if Microsoft is going to develop a update to fix this problem. ---------------- This post is a suggestion for Microsoft, and Mic...

Surpressing plot of blank cells
I'm creating a line chart plot using Excel 2007. The data range for plot lines contains some blank cells. These blank cells get plotted as zero points. How do I change this behavior to plot only points that have values and to leave the blank cells unplotted? I appreciate your help, -John Hi, If they are truly blank then check the plot empty cells setting via. Select Data > Hidden and Empty cells. If the Cells in fact contain formula then use NA() instead of "". This will suppress the data marker but will not break the line. Alternatively use Autofilter to hide un...

Populate cells with a unit price
For business purposes. I would like to put an item or model # in one cell and have the price for this particular item automatically populate the cell to it's right. because each sale could be different I want to the form to populate the unit price for any item that I input in a particular cell Hope this makes sense Thanks Hi Luka You need to take a look at the VLOOKUP function. You will find an easy explanation at Debra Dalgleish's site http://www.contextures.com/xlFunctions02.html -- Regards Roger Govier "luka" <luka@discussions.microsoft.com> wrote in me...

Changing cell values through form
Hi... I'm not that good with access so I decided to look for help here. I have a table filled with drivers. Starting number, Driver name, and Number of laps are the columns. My Idea is to make a form that will list all the drivers, and add a value of +1 into the "Number of laps" cell to that driver when double clicked. That would be a sort of Lap counter form... Every suggestion is welcome. -- ::: www.ktvj-online.com - Motocros Portal ::: ::: www.forum.ktvj-online.com - Forum ::: ...

Fixed Number of Columns
After manipulating data in Excel I would like to copy and paste the data in a text editor. The data should be in fixed format, for example integer numbers should end in columns with multiples of 5. The text is then used as input to an old fashioned program which requires that the data be aligned I changed the font in Excel to Courrier New and then selected a column width of 5. When I copy the data to a text editor it copies some tab marks and the columns do not align. How can I make things align in the text editor? Vino, You can use a macro: copy the code below, then paste into a module...

Embedding Word table in Excel cell
Is there any way in which I can embed a small MS Word table in an Excel cell? Thanks. -- Larry Lester Copy / paste as a picture. Pictures can always be re-sized and re-positioned -- Gary's Student "Larry Lester" wrote: > Is there any way in which I can embed a small MS Word table in an Excel cell? > Thanks. > -- > Larry Lester Gary's reply works for pasting the table in as a graphic object _on_ an XL sheet, but you cannot paste a graphic into a cell as cell content. If you simply Paste to a cell, the corresponding number of cells will be filled with th...

format numbers in hexadecimal
I'd like to display numbers in hexadecimal and still be able t calculate with them. DEC2HEX() and HEX2DEC() are available as functions (I think they're i the Analysis ToolPak) but I'd rather avoid adding these to ever calculation. That is, for example, I don't want to have to change every formula "=A1+A2" to "=dec2hex(hex2dec(A1)+hex2dex(A2))". Any idea how to add "Hex" as another numeric format choice, so tha numbers are displayed directly in hex -- Message posted from http://www.ExcelForum.com stan_shepherd wrote... >I'd like to ...

Picking Random Numbers
I have been tasked with coming up with a form that will automatically assign a random number to an individual with the click of a button. The number is 7 digits in length. Each element of the number can range from 1 to 9. Something simular to a lottery number picker. Does anyone have any sample code that I can refer to? Thanks in advance! Dwight Function GenerateRandomNumber() As String Dim lngLoop As Long Dim strResult As String Randomize For lngLoop = 1 To 7 strResult = strResult & Format((Int(9 * Rnd) + 1), "0") Next lngLoop GenerateRandomNumber = strResu...

how to find numbers that give another number
hi all. I need to make a macro which look up a column of an excel paper and find the numbers that once summed give the number in cell b5 for example!!they could be 2 or 3 ..or 4..and so on!! You would need the solver http://tinyurl.com/4doog example -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Rossella" <rossy77@gmail.com> wrote in message news:1145464775.044405.243700@v46g2000cwv.googlegroups.com... > hi all. > I need to make a macro which look up a column of an excel paper and > find the numbers that once summed give the number in ce...

can not accses my email i get Error Number: 0x800CCC32
Unable to poll for new messages on your HTTP server. Account: 'Hotmail', Server: 'http://services.msn.com/svcs /hotmail/httpmail.asp', Protocol: HTTPMail, Server Response: 'Authorization Required', Port: 0, Secure(SSL): No, Error Number: 0x800CCC32 abe <anonymous@discussions.microsoft.com> wrote: > Unable to poll for new messages on your HTTP server. > Account: 'Hotmail', Server: 'http://services.msn.com/svcs > /hotmail/httpmail.asp', Protocol: HTTPMail, Server > Response: 'Authorization Required', Port: 0, Secure(SSL): ...

non owner adding attendees to an activity hangs calendar
Any time we have a non owner of an activity adding a contact as a required or as an optional attendee to an activity in CRM, the calendar and the service calendar both hang - not just for that user, but for the entire organization, until an issreset is performed. Also an error pops up with a generic database fault message, not very descriptive. Previous attempts at stopping/restarting services resulted in hanging services. This seems like a software bug - unhandled permissions error or something maybe? Any thought or ideas are welcome on what we should do to get this issue resolved....

Need text in upper left corner of a cell
Hi Is it possible to have some text written in upper corner of a cell? I use Excel as a fill-in userform. I can justify the size of other columns, but it would be nice to write something into the cell, so the user understand what to fill inn. Any workarounds or other suggestions? Any help is appreciated. Gunnar Hi maybe you can use the comment functionality (goto 'Insert - Comment') to add some hints for the user -- Regards Frank Kabel Frankfurt, Germany Gunnar wrote: > Hi > > Is it possible to have some text written in upper corner > of a cell? > I use Excel a...

Exporting As Text with X Number of Spaces Between Data
Is there a way to export data in a MS Excel spreadsheet as text and define the amount of spaces between the data being exported? The number of spaces need to vary. This concept is the opposite of importing text as "Fixed Width" and creating column break lines. Instead, I'd like to export columns into a text file and specify the amount of spaces between the text that is being exported from each column. The amount spaces in the text file needs to be different between each set of column data. Thanks! One way: Say you have 5 columns, and you want 2 spaces between A &...

click on checkbox copy to active cell
Is there any way to click on a checkbox beside a row of information and copy that row of data to an active cell? Maybe. If your active cell is in column A, you can copy that whole row to the activecell's row. If you use a checkbox from the forms toolbar, you can add as many as you want to the worksheet. But assign each of them the same common macro: Option Explicit Sub testme01() Dim myCBX As CheckBox Dim myCopyObjectsWithCells As Boolean Set myCBX = ActiveSheet.CheckBoxes(Application.Caller) If myCBX.Value = xlOn Then If myCBX.TopLeftCell.Row = Ac...

how do I display cell contents when I am over 32, 767 characters?
Excel was not designed for this. Depending on your purpose, you should use Word or Access. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "usdrmd" <usdrmd@discussions.microsoft.com> wrote in message news:E30267BD-2CAF-4FC7-83A2-F9E79F7CFE06@microsoft.com... > ...

Getting info from the last used cell
Currently I am adding many sheets together. I am using "Start Sheet" and "End Sheet" and everything in between those two sheets is adding for a total. I would (in a few cells) to just get the data from the last cell used. Example: The sheets I am adding are dates and if sheet 01-19-09 (dd-mm-yy) has the last entry in cell c18 I would like that number. However there may be blank cell before that sheet. and after that sheet there will be info in other cells. I would like that info to go into c18 of the sheet I use and named "Totals". -- Ed Davis ...

Excel Cell Data Limit
I am creating an ASP.NET page to dump data to an excel file. This works fine, but when I open the excel file I get an error that says 'the cell data is too large'. I heard from somebody that excel cells can hold up to 32,000 character, but can only display 1,024 characters in the formula bar. What restrictions does this impose on the data that cannot be displayed? If I do a keyword search, is that data still going to get searched? Thanks in advance, Ryan hil from excel help. Type specifications. 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula...

How can I set up the random selection of a cell from within a ran.
I want Excel to randomly select one cell from a range of cells, but is it possible? This would be the equivalent of drawing one slip of paper from several hundred. An uncomplicated way to do this is to simply add a column filled with the RAND() function next to your column of choices. Select both columns, and sort on the Rand() column. Pick the one at the top, or bottom, as your random selection. A little more complicated procedure would be to use a formula, referring to this combination of Rand() and your list, and let this formula randomly pick an item. Place your list in an "out o...

saving with name from cell #3
That one works perfectly!! Thanks again! Dank u de veer!! Ja -- jama ----------------------------------------------------------------------- jamaz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1663 View this thread: http://www.excelforum.com/showthread.php?threadid=31504 You are welcome -- Regards Ron de Bruin http://www.rondebruin.nl "jamaz" <jamaz.1fxv5b@excelforum-nospam.com> wrote in message news:jamaz.1fxv5b@excelforum-nospam.com... > > That one works perfectly!! > > Thanks again! > > Dank u de veer!! > >...

Add number of days due field on aged trial balance reports (PM/RM)
I would like to add 'number of days past due' field on the aged trial balance reports and sort the reports by this field. I dont know how to proceed. Can someone guide me? ...

cell reference #2
I have a list of numbers in column A. I wish at the bottom to return the maximum value in column A using =max(a1:a100) but i also want the cell reference of this value. how can i do this. thanks Use the MATCH function - it will give you the relative position of the maximum value in your range, and as this begins at A1 then this is the same as the row. If you have two maximum values, however, it will only report the first occurrence. Hope this helps. Pete lees news wrote: > I have a list of numbers in column A. I wish at the bottom to return the > maximum value in column A using =m...

Sum every other cell? #2
I just previewed the post below and saw that the format didn't hold. don't know how to make it look right. I hope you can figure it out fro what you see. Sample rows: Day 1 Day 2 Day 3 Category #Books $$$ #Books $$$ #Books $$$ 1 4 16.00 0 0.00 7 29.95 2 3 17.50 3 17.50 3 17.50 3 0 0.00 6 24....

search and return cell value
I hope that someone may be able to help. I have a worksheet that has 7 fixed columns and a variable number of rows. The rows can range from 1 to many. Cell A1 is dependent upon the value in the last row in column G. I can locate this with nested "if" statements or by creating a new column H that will test to see if the next row contains values. Is there any function that I can put in cell A1 that will locate the last row in column G that contains a value and return the value of this cell? Many Thanks Hi if you have no blank rows in between try =OFFSET($G$1,COUNTA(G:G)-1,0) -- Reg...