+ Reply to Thread
Results 1 to 11 of 11

Delete cells based on Prefix

Hybrid View

Will03 Delete cells based on Prefix 10-20-2014, 12:02 PM
stnkynts Re: Delete cells based on... 10-20-2014, 12:21 PM
Will03 Re: Delete cells based on... 10-20-2014, 02:49 PM
JOHN H. DAVIS Re: Delete cells based on... 10-20-2014, 02:53 PM
Will03 Re: Delete cells based on... 10-20-2014, 03:08 PM
JOHN H. DAVIS Re: Delete cells based on... 10-20-2014, 03:13 PM
Will03 Re: Delete cells based on... 10-20-2014, 03:28 PM
JOHN H. DAVIS Re: Delete cells based on... 10-20-2014, 03:44 PM
Will03 Re: Delete cells based on... 10-21-2014, 08:38 AM
JOHN H. DAVIS Re: Delete cells based on... 10-21-2014, 08:42 AM
Will03 Re: Delete cells based on... 10-21-2014, 11:19 AM
  1. #1
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Guadalajara, Mexico
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    101

    Delete cells based on Prefix

    I have a macro that currently deletes blank rows in column A and then a few unnecessary columns E and G. The report has changed on me and now in column E I must delete all the locations that begin with "MX " and there is just too many to be selecting one by one. Is there anything I can add to this current macro to delete all Locations that begin with "MX "? there are many other prefixes such as "US" "CA" but the one I need to delete are "MX" the whole row where the MX is found in column E needs to be deleted...

    Sub Delete()
    
        Dim cRow As Long
        cRow = 3
        Do While (IsEmpty(Cells(cRow, 1)))
        cRow = cRow + 1
        Loop
        cRow = cRow - 1
    
        Columns("E:E").Select
        Selection.Delete Shift:=xlToLeft
        Columns("G:G").Select
        Selection.Delete Shift:=xlToLeft
    
        if range("E" & cRow).value like "MX*" then 'OR left(range("E" & cRow).value, 2) = "MX"
           Rows("3:" & cRow).Delete Shift:=xlUp
    
        end if 
    
    End Sub

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Delete cells based on Prefix

    Maybe:

    Sub Delete()
    
        Dim cRow As Long
        cRow = 3
        Do While (IsEmpty(Cells(cRow, 1)))
        cRow = cRow + 1
        Loop
        cRow = cRow - 1
    
        Columns("E:E").Select
        Selection.Delete Shift:=xlToLeft
        Columns("G:G").Select
        Selection.Delete Shift:=xlToLeft
        
        Dim i As Long
        For i = Range("E" & Rows.Count).End(xlUp).Row To 3 Step -1
            If Left(Range("E" & i), 2) = "MX" Then
                Range("E" & i).EntireRow.Delete Shift:=xlUp
            End If
        Next i
    
    End Sub

  3. #3
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Guadalajara, Mexico
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    101

    Re: Delete cells based on Prefix

    Its not deleting the MX cells. Actually let me clarify something, the contents of the cells begins with "MX" but has a longer name than that like this "MX Agus buliding 1" or "MX Agus Building 18" or "MX GDL North Campus" and so forth but the prefix is all the same "MX"...

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Delete cells based on Prefix

    Can you post a sample workbook with some dummy data?

  5. #5
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Guadalajara, Mexico
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    101

    Re: Delete cells based on Prefix

    Here is the file... Thanks...

  6. #6
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Delete cells based on Prefix

    You wouldn't be referring too Column F would you?

  7. #7
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Guadalajara, Mexico
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    101

    Re: Delete cells based on Prefix

    Yes its column F... before I was deleting the E column and the F became E but its ok we can leave it. Its not absolutely necessary to delete it. Basically to review what the macro needs to do is this...

    1. delete all blank rows in the employee ID column A until we get to the first populated cell in column A
    2. delete all the MX prefixes from column F and leave all others alone "RTS" "US" and "CA" in particular before I would un-check those options and delete all the other and just recheck them to make them show again

    Thanks
    Last edited by Will03; 10-20-2014 at 03:31 PM.

  8. #8
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Delete cells based on Prefix

    Maybe:

    Sub Will03()
    Dim x As Long
    Dim y As Long
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    x = ActiveSheet.UsedRange.Rows.Count
    y = 0
    Range("A2").Select
    Do Until ActiveCell.Value <> "" Or y >= x
        y = y + 1
        Rows(ActiveCell.Row).Delete
    Loop
    Range("F1:F" & Range("F" & Rows.Count).End(3).Row).AutoFilter 1, "=MX*"
    Range("F2:F" & Range("F" & Rows.Count).End(3).Row).SpecialCells(12).EntireRow.Delete
    ActiveSheet.AutoFilterMode = False
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
    End Sub
    Last edited by JOHN H. DAVIS; 10-20-2014 at 03:46 PM.

  9. #9
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Guadalajara, Mexico
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    101

    Re: Delete cells based on Prefix

    Hello John when I copy the macro to the VBA editor, and run it all it does is cycle like if it were opening up excel over and over again and once it completes, it leaves the worksheet without any changes... Any advise?

  10. #10
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Delete cells based on Prefix

    Where did you place the code? Should be in a Standard Module.

  11. #11
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Guadalajara, Mexico
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    101

    Re: Delete cells based on Prefix

    Yes i put it in a Standard Module...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Insert prefix based on cell value.
    By Spicey_888 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-03-2014, 08:00 PM
  2. [SOLVED] Delete workbooks with specific prefix in its name
    By Manish84 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-27-2014, 06:07 PM
  3. Add Standard Prefix To Cells
    By zulhfreelancer in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 5
    Last Post: 06-09-2013, 07:11 AM
  4. Vlookup based on Prefix
    By yawnzzzz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-11-2009, 05:46 PM
  5. Using array to sum by multiple criteria including one based on prefix
    By monkdelafunk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-29-2008, 02:24 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1