Solved

Excel Function

Posted on 2011-09-27
7
195 Views
Last Modified: 2012-05-12
I require an excel function that can do the following.

Scan a sheet that I specifiy in the VBA in code an filter the data currently sheet by looking through specific columns that I specify and criteria that I can specify.  For example:

Sheet = DataSheet
Column A = >1000
Column C - Must Contain the following string ("google")
Column E <> 12345 Or 54321
Column F = "marketshare"

The rows that meet that criteria are to removed from the datasheet.

Please keep function as generic as possible will would like to use filter through multiple datasheets all of have varing amounts of columns.

Commenting would be cool, cya.
0
Comment
Question by:Zack
7 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36708494
Hello,

what code have you come up with so far? Where did you get stuck? Which aspect of the VBA do you need help with?

Can you post your code?

0
 
LVL 5

Expert Comment

by:DerZauberer
ID: 36708496
Uhm isn't that a standard excel function?

Select whole sheet, use the normal Data/Filter with maybe userdefined criterias for the columns you want, then delete resulting rows... done...

If you need VBA code, record a macro for that.
0
 

Author Comment

by:Zack
ID: 36708661
Sure this what I have so far.

Sub RowFilterDeletion()
    Dim startrow As Long
    Dim endrow As Long
    Dim currentrow As Long
 
    With ActiveSheet

        startrow = 10
        endrow = 50

        For currentrow = endrow To startrow
           
            With .Cells(currentrow, "A")

                If Not IsError(.Value) Then

                    If .Value = "1000" Then .EntireRow.Delete            

                End If

            End With

        Next currentrow

    End With

End Sub

Not to sure about adding additional criteria that I am after.
Thanks
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 32

Expert Comment

by:Rob Henson
ID: 36708725
Using the standard AutoFilter, depending on the order of the selection of criteria; it would filter for criteria1 AND criteria2 AND criteria3 etc etc.

You could use the Advanced Filter (AF) function to copy the data you want to keep to a different sheet, thus leaving the original data intact, to be deleted if so required.

The criteria for AF is specified in a separate table, using column headers from the data. Where two items of criteria are placed on the same row within this table, the filter takes it as criteria1 AND criteria2. If placed on separate rows, it treats it as criteria1 OR criteria2.

So to get an AND style comparison your criteria table would be:

Col A      Col C         Col E         Col F
>1000    *google*  <>12345   marketshare
>1000    *google*  <>54321   marketshare

To get an OR comparison:

Col A      Col C         Col E         Col F
>1000    
              *google*  
                               <>12345
                               <>54321
                                                 marketshare

The Col A, C, E & F references will have to be replaced with the column headers from the data. If necessary these could be linked to the data headers as dropdowns so that the user can select the column header for which they want to do a filter.

The AF routine from the Data menu would then need 3 references to function:

Ref 1:  Data Area
Ref 2:  Criteria area
Ref 3:  Destination for copied data, if you want all columns from the data make this area the same number of columns wide as the original.

Thanks
Rob H
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 36708763
Looking at it, you want to removce the data with those criteria or in other words keep the data that does not meet those criteria.

You would have to change the criteria:

<=1000
<>*google*
=12345
=54321
<>marketshare

Thanks
Rob H
0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 500 total points
ID: 36708918
Your code is a reasonable start.  If you want to do it this way rather than using a filter, which can cause problems with screen display when running vba code, then you could add a match function where you define your criteria.  I prefer to do it this way as it's easier to see what's going on, and easier for you to change later to add more citeria, or change the existing ones.  This isn't exhaustively tested but I think it is pretty much right:

Sub RowFilterDeletion()
    Dim startrow As Long
    Dim endrow As Long
    Dim currentrow As Long
 
    With ActiveSheet

        startrow = 10
        endrow = 14

        For currentrow = endrow To startrow Step -1
            If FilterMatch(.Rows(currentrow)) Then
                .Rows(currentrow).EntireRow.Delete
            End If
        Next currentrow
    End With
End Sub

Private Function FilterMatch(ByRef rw As Excel.Range) As Boolean

'Column A >= 1000
'Column C - Must Contain the following string ("google")
'Column E <> 12345 Or 54321
'Column F = "marketshare"

    FilterMatch = False
    
    If IsError(rw.Cells(1, 1).Value) Then Exit Function
    If Not (rw.Cells(1, 1).Value >= 1000) Then Exit Function
    If Not (rw.Cells(1, 3).Value = "google") Then Exit Function
    If Not ((rw.Cells(1, 5).Value = 12345) Or (rw.Cells(1, 5).Value = 54321)) Then Exit Function
    If Not (rw.Cells(1, 6).Value = "marketshare") Then Exit Function
    
    FilterMatch = True
    
End Function

Open in new window

As you can see, the function quits, returning False as soon as one does not match, so you don't waste time checking additional criteria. The other strength of this approach is that you can make your criteria quite complex in ways that would difficult to do with built-in filtering.
0
 

Author Closing Comment

by:Zack
ID: 36709101
Thank you for your assistance.

I be sure to post my starting code in the future I didn't mean to offend anyone.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
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…

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now