• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 742
  • Last Modified:

Excel function to delete columns and check rows for string

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
badtz7229
Asked:
badtz7229
  • 5
  • 4
  • 4
1 Solution
 
StephenJRCommented:
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
 
gtglonerCommented:
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
 
badtz7229Author Commented:
sorry, where do i add this script in excel?  i totally forgot.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
gtglonerCommented:
OOppps, in  line 10 of my code, change "Subcategory" to "Sub Category"
0
 
StephenJRCommented:
Alt + F11 from the worksheet to open Vb editor window. Then Insert > Module and paste code into window.
0
 
gtglonerCommented:
You click Alt + F11, click the sheet where your data is, then copy and paste the code to the big code window
0
 
gtglonerCommented:
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
 
badtz7229Author Commented:
thx StephenJR: that worked.
0
 
badtz7229Author Commented:
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
 
StephenJRCommented:
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
 
badtz7229Author Commented:
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
 
StephenJRCommented:
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
 
badtz7229Author Commented:
ah. ok. thanks that clarifies it for me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 5
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now