Solved

Excel function to delete columns and check rows for string

Posted on 2011-02-18
13
732 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

733 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