• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help to remove waste data from the sheet.

J

Jagdev Singh

Active Member
Hi Experts
I have a excel sheet with contains data which I need to delete from each section of the sheet.
** PART OF
USD
AND COMPRISES THE FOLLOWING POSTINGS
1.00
A
123.00
A
123.00
A
14.00
A
12.00
A
12.00
A
12.00
A
12.00
A
11.00
A
12.00
A
12.00
A
12.00
A
12.00
A
12.00
A
12.00
A
12.00
A
12.00
A
12.00
A
The section is available in the sheet in 3 sections. Instead of deleting it manually can we delete via VBA. I tried to record the macro, but it doesn’t fulfill the need. The common thing in the section is "AND COMPRISES THE FOLLOWING POSTINGS "

Code: 
Sub Macro1()
'
' Macro1 Macro
'
'
  Range("E3").Select
  Selection.Copy
  Cells.Find(What:=" AND COMPRISES THE FOLLOWING POSTINGS", After:=ActiveCell _
  , LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
  SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
  Range("A3:M21").Select
  Application.CutCopyMode = False
  Selection.Delete Shift:=xlUp
End Sub
 

Attachments

Last edited:
Hi JD,

Can you try below code:

Code: 
Option Explicit

Sub Macro1()

Dim lastrow As Long

lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    Range("A1").AutoFilter
    ActiveSheet.Range("$A$1:$L$" & lastrow).AutoFilter Field:=3, Criteria1:="<>"
    Range("A2:L" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Range("A1").AutoFilter
    ActiveSheet.Range("$A$1:$L$" & lastrow).AutoFilter Field:=5, Criteria1:= _
        "AND COMPRISES THE FOLLOWING POSTINGS"
    Range("A2:L" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Range("A1").AutoFilter
End Sub

Regards,
 
Hi Somendra

I need small amendment in the code. This code should work from Sheet3 of the workbook and can it be implemented in the sheets available after sheet3 as well. The data will be same from sheet3 and till the rest of the sheets

Regards,
JD
 
Jagdev Singh said:
Hi Somendra

I need small amendment in the code. This code should work from Sheet3 of the workbook and can it be implemented in the sheets available after sheet3 as well. The data will be same from sheet3 and till the rest of the sheets

Regards,
JD
Click to expand...
Sorry, I did not understood. Can you demonstrate on a sample file.

Regards,
 
Hi Somendra
Sorry for the confusion. I was comparing the sample file with the live data. I will be using the above code on Sheet3 of my live data. So I will be changing the current code
This - lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
To - lastrow = Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row
Now I am looking to add loops in the sheets from sheet3 till the count of sheet available in the workbook. I amended the sample file to help you understand the concept bit in depth.
Regards,
JD
 

Attachments

You must log in or register to reply here.
Back
Top