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
Solved

Excel function to delete columns and check rows for string

Posted on 2011-02-18
13
730 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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…

856 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