Solved

Excel function to delete columns and check rows for string

Posted on 2011-02-18
13
729 Views
Last Modified: 2012-06-27
I would like a script that when executed will delete colums  Acdfhjlnopq
and from the remaining (now new) cells will check if
cell Ax = "Category" or "Sub Category" then delete row x
Cell Bx = "UserID" or "Subject" then delete row x
Test.xls
0
Comment
Question by:badtz7229
  • 5
  • 4
  • 4
13 Comments
 
LVL 24

Accepted Solution

by:
StephenJR earned 500 total points
ID: 34927824
Here is one approach:
Sub x()

Dim r As Long

Range("A:A,C:D,F:F,H:H,J:J,L:L,N:Q").EntireColumn.Delete

For r = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
    If Cells(r, 1) = "Category" Or Cells(r, 1) = "Sub Category" Or _
       Cells(r, 2) = "UserID" Or Cells(r, 2) = "Subject" Then
       Cells(r, 1).EntireRow.Delete
    End If
Next r

End Sub

Open in new window

0
 
LVL 17

Expert Comment

by:gtgloner
ID: 34927852
Try this code:
Sub Macro1()

    Range("A:A,C:C,D:D,F:F,H:H,J:J,L:L,N:Q").Select
    Range("L1").Activate
    Selection.Delete Shift:=xlToLeft
    
    Range("A1").Select
    While Not IsEmpty(Selection)
    
     If ActiveCell.Value = "Category" Or ActiveCell.Value = "Subcategory" Then
     
      ActiveCell.EntireRow.Delete
      ActiveCell.Offset(1, 0).Select
      
     End If
     
    Wend
    
    Range("B1").Select
    While Not IsEmpty(Selection)
    
     If ActiveCell.Value = "UserID" Or ActiveCell.Value = "Subject" Then
     
      ActiveCell.EntireRow.Delete
      ActiveCell.Offset(1, 0).Select
      
     End If
     
    Wend
    
End Sub

Open in new window

0
 

Author Comment

by:badtz7229
ID: 34927862
sorry, where do i add this script in excel?  i totally forgot.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 17

Expert Comment

by:gtgloner
ID: 34927873
OOppps, in  line 10 of my code, change "Subcategory" to "Sub Category"
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 34927881
Alt + F11 from the worksheet to open Vb editor window. Then Insert > Module and paste code into window.
0
 
LVL 17

Expert Comment

by:gtgloner
ID: 34927883
You click Alt + F11, click the sheet where your data is, then copy and paste the code to the big code window
0
 
LVL 17

Expert Comment

by:gtgloner
ID: 34927907
My previous code is faulty, use this one instead:
Sub Macro1()

    Range("A:A,C:C,D:D,F:F,H:H,J:J,L:L,N:Q").Select
    Range("L1").Activate
    Selection.Delete Shift:=xlToLeft
    
    Range("A1").Select
    While Not IsEmpty(Selection)
    
     If ActiveCell.Value = "Category" Or ActiveCell.Value = "Sub Category" Then
     
      ActiveCell.EntireRow.Delete
      ActiveCell.Offset(1, 0).Select
      
     End If
     ActiveCell.Offset(1, 0).Select
    Wend
    
    Range("B1").Select
    While Not IsEmpty(Selection)
    
     If ActiveCell.Value = "UserID" Or ActiveCell.Value = "Subject" Then
     
      ActiveCell.EntireRow.Delete
      ActiveCell.Offset(1, 0).Select
      
     End If
     ActiveCell.Offset(1, 0).Select
    Wend
    
End Sub

Open in new window

0
 

Author Comment

by:badtz7229
ID: 34927939
thx StephenJR: that worked.
0
 

Author Comment

by:badtz7229
ID: 34928183
StephenJR: may i ask in your then-statement why do u say
Cells(r, 1).EntireRow.Delete

it kinda reads to me if columns A and columns B contain any of the specified value
then delete column A. am i understanding cell (r,1) to = column A?
please advise.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 34928716
The EntireRow bit means that it will remove the entire row containing cells(r,1). So, if r=4 we're in the fourth row and if either A4 (cells(4,1)) or B4 (cells(4,2)) contains those items it will delete the whole row. Does that help?
0
 

Author Comment

by:badtz7229
ID: 34929099
yes, but when u say delete (r.1) isn't this always going to delete from column A bc of the 1 ?
or does the value 1 mean something else other than location of column?
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 34929137
But it deletes every column, i.e. the whole row, which I thought was what you wanted.

In this line

Cells(r, 1).EntireRow.Delete

we could substitute any number for 1, e.g.

Cells(r, 5).EntireRow.Delete

Cells(r, 100).EntireRow.Delete

It's just saying delete the rth row.

0
 

Author Comment

by:badtz7229
ID: 34929518
ah. ok. thanks that clarifies it for me.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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.

810 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