?
Solved

delete cells based on value

Posted on 2012-08-22
4
Medium Priority
?
524 Views
Last Modified: 2012-08-22
I thought the attached macro would delete rows based on the values of a column of cells that I have preselected but it seems to delete far more than it should.  I want it to delete only those cells that have dates before March 1 2012. March1 has the julian value of about 41000.
Any suggestions on a modification?
Thanks.

Sub Deleteit()
Dim c As Range
For Each c In Selection
If c.Value = Range("$A$1").Value < 41000 Then c.EntireRow.Delete
Next
End Sub

Open in new window

Sub Deleteit()
Dim c As Range
For Each c In Selection
If c.Value = Range("$A$1").Value < 41000 Then c.EntireRow.Delete
Next
End Sub

Open in new window

Deleted by SouthMod, no points refunded:  8/22/2012 4:32:37 AM
Duplicate: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27837464.html
RA: http://www.experts-exchange.com/R_10146.html
0
Comment
Question by:willie108
4 Comments
 

Accepted Solution

by:
www_exacto_dk earned 1200 total points
ID: 38319534
Hi,

It can probably be done more elegant, but how about something like this:

Sub delete_cells()
   
    Dim cl As Range
   
    For Each cl In Selection
        cl.Activate
        Do While ActiveCell.Value < 10 And ActiveCell.Value <> ""
            ActiveCell.EntireRow.Delete
        Loop
    Next cl
   
End Sub

Brg
Martin
0
 
LVL 18

Assisted Solution

by:krishnakrkc
krishnakrkc earned 600 total points
ID: 38319728
Hi,

Try

Sub kTest()
    
    Dim dtDate  As Date
    
    Application.ScreenUpdating = False
    
    dtDate = [a1]
    With Selection
        .Columns(1).EntireColumn.Insert
        With .Cells(1).Offset(, -1).Resize(.Rows.Count)
            .FormulaR1C1 = "=countif(rc[1]:rc[" & Selection.Columns.Count & "],""" & dtDate & """+0)"
            .Value = .Value2
            .Replace 0, vbNullString, 1
            On Error Resume Next
            .SpecialCells(2, 1).EntireRow.Delete
            .EntireColumn.Delete
        End With
    End With
    
    Application.ScreenUpdating = True
    
End Sub

Open in new window


Kris
0
 
LVL 19

Assisted Solution

by:Arno Koster
Arno Koster earned 200 total points
ID: 38320242
keep in mind that when you want to select on dates before 1st of march, 2012 it would be wise to also incorporate date formatting in it, otherwise the value 123 will be deleted as well.

you could change line 4: if c.Value = Range("$A$1").Value < 41000 Then c.EntireRow.Delete to

if c.Value = Range("$A$1").Value < 40969 and c.numberFormat = "m/d/yyyy" Then c.EntireRow.Delete

Open in new window


update the numberformat to the one you are using.
0
 

Author Closing Comment

by:willie108
ID: 38323372
Thanks all.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question