Solved

Excel function to delete columns and check rows for string

Posted on 2011-02-18
13
725 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
 
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
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 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

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

919 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

22 Experts available now in Live!

Get 1:1 Help Now