First post!

I have a spreadsheet with many rows and many columns where I want to delete entire rows based on a criteria to be found in one specific column. The column with the criteria to be used is sorted therefore all of the rows with that criteria are grouped.
In previous attempts to delete using information from previous posts, I ended up skipping rows and only deleting half of what I need to delete.
Do you have an idea of what I can do?

Thank you in advance for your help and expertise.

Hi all,

I am hoping someone may be able to assist or at least points me in the right direction with this.

I would like a macro that will delete entire rows within a worksheet (Sheet1) where a certain value appears in a cell within Column A.

The values in Column A (to which the row is to be deleted), are listed in Column C of Sheet2 - the # of criteria listed in Sheet2 C:C, as well as the values themselves change every month.

Ultimately, I would like to update the list in Sheet2 with the criteria that I would like the macro to search for in Sheet1 and, if any appear in ColumnA of Sheet1, to delete the entire row.

Any assistance would be much appreciated.

I am trying to figure out how to have some VBA look down Column 1 and where the Cell's string value = "GRANDTOTAL", to DELETE that ROW and ALL ROWS below.

Can anyone help me out in writing the Visual Basic code?

Thank You In Advance.

Please Register to Remove these Ads

Hello,

I'm quite a novice here. I'm trying to delete rows where column C contains specific text in some instances. In other cases I am trying to delete rows where specific text is found in column C AND column M contains the year 2009 (in format dd-mm-yyyy).

The code I'm working with so far is as follows:

Code:

Sub DeleteUnwanted()
'
' DataSheetProcess Macro
'

'This part works OK
Sheets("Sheet1").Select

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

'start incrementing
For y = LastRow To 2 Step -1

If Cells(y, "C").Value = "Germany Domestic" Then Rows(y).EntireRow.Delete

'The following is not working correctly

If Cells(y, "C").Value = "Intl Corporate" And EntryDate.Cells(y, "M").Value = 2009 Then Rows(y).EntireRow.Delete

Next y
'Call FindZeroReplace
End Sub

Is it possible to delete entire rows between rows 4 and the last row of data where columns F & G are either both blank or both zero? I have already define the lastrow of data as the variable "lastrow".

Any ideas would be great
Thanks

How can I delete rows with a specific abbreviation. I have thousands of rowns with different information in each colum. The first column of each row has a certain abbreviation (SF, SP, FP, etc....). How do I delete the entire rows that have SP in the first column.


SP 10 asdf
SP 3 fgeg
FP 8 lpoj

In this example I want the first two rows completly delted?

Thanks.

Hi all

Following on from my previous query (now resolved), I now want to be able to have a macro that will automatically delete all rows that aren't for the area's that I'm interested in reporting on.

In column A are a list of area codes, such as AF614, AF414, EH130, C282, etc. I want the macro to automatically remove any rows where the area code doesn't begin with AF.

I found one macro that worked, but despite the instructions in the macro saying you could have multiple terms that it would check and delete, it would only delete the first term, and ignore all the others. As a consequence it meant using it was totally impractical. I've pasted it below for you to see - a minor adjustment or complete alternative may be required?

Many thanks
Surreybloke

Sub Delete_Based_on_Criteria()

' This macro will delete an entire row based on the presence of a
'predefined word or set of words. If that word or set of words is
'found in a cell, in a specified column, the entire row will be 'deleted Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim FoundRowToDelete As Boolean
Dim OriginalCalculationMode As Long
Dim RowsToDelete As Range
Dim SearchItems() As String
Dim DataStartRow As Long
Dim SearchColumn As String
Dim SheetName As String

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Choose the row you want the search and delete to start on
' Choose the column to search and delete to use for deletion
' Choose the sheet in the workbook you want this macro to be run on
DataStartRow = 1
SearchColumn = "B"
SheetName = "Sheet1"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Enter the terms you want to be used for criteria for deletion
' All terms entered below are CASE SENSITIVE and need to be
'seperated by a comma
SearchItems = Split("INPUT TEXT HERE, INPUT TEXT HERE", ",")
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

On Error GoTo Whoops
OriginalCalculationMode = Application.Calculation
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, SearchColumn).End(xlUp).Row
For X = LastRow To DataStartRow Step -1
FoundRowToDelete = False
For Z = 0 To UBound(SearchItems)
If InStr(.Cells(X, SearchColumn).Value, SearchItems(Z)) Then
FoundRowToDelete = True
Exit For
End If

Next

If FoundRowToDelete Then
If RowsToDelete Is Nothing Then
Set RowsToDelete = .Cells(X, SearchColumn)
Else
Set RowsToDelete = Union(RowsToDelete, .Cells(X, SearchColumn))
End If

If RowsToDelete.Areas.Count > 100 Then
RowsToDelete.EntireRow.Delete
Set RowsToDelete = Nothing
End If
End If

Next

End With
If Not RowsToDelete Is Nothing Then
RowsToDelete.EntireRow.Delete
End If
Whoops:
Application.Calculation = OriginalCalculationMode
Application.ScreenUpdating = True

End Sub

I have four workbooks. The master workbook has a file name of MPF.xls Using
the Master Workbook, I want to copy entire row(s) from the other three
workbooks (Mets.xls, Day.xls, and Courier.xls) to the master workbook when
specific criteria is met. I want to copy the rows when column 5 = Robin and
or Column 9 = Daycare.

Hi all,
I'm currently working on an excel sheet and need a macro that can delete entire rools based on values in column B. Attached is a sample of the sheet, row 1 to 72 is a letter template, but worksheet functions (Vlookup) is used to determine the values in row 24 to 54.
I need a macro that will delete entire between rows 24 to 54 that have no value and if all the rows (24 to 54) has no value it should delete rows 1 to 72.

Rows 73 to 144 is another letter template, among which rows 96 to 126 are the rolls that the values are determine by worksheet functions. Macro should also do same as the above template. this also apply to the remaining templates as i have up to 30 letter templates on the excel sheet.

Many thanks

Hello all. I have a spreadsheet that is over 500 rows long. As it is I have
no use for all of the rows at the same time and have to keep hiding and
showing them as need arises. Is there a macro to hide chunks of it based on
the value of one cell of the row? In other words, en each row I will have a
formula like =if(a1=0,"HIDE","") and this value will tell the macro wether to
hide the row or not. I tried case.select but it takes a LONG time and I would
have to write a piece of code for every line. FYI, the rows that need hiding
will be in sequence, in other words, from 100 to 500, or from 80 to 500,
depends on where the last text was entered. Thanks in advance.