Clear range of cells rather than delete rows VBA

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

I've got two quick questions.

I've got some code which deletes entire rows, if a word is present in column A (see below).

1) Do you know how I can modify the code to delete a range of cells ie A5:AC100000 instead of deleting the entire row where it finds the word specified?

2) How can I amend the code below so it refers to cell B4 in a file that I'm importing? The file being imported is declared as a workbook (Dim importWB As Workbook) and is set using "Set importWB = ActiveWorkbook" whenever it's referred to. If you'd like the long version of the code, please let me know.

Thanks in advance.


Sub DeleteRows()


' DELETES ALL ROWS FROM A2 DOWNWARDS WITH THE WORD "Coffee" IN COLUMN A
'========================================================================
Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "A").Value) = "0032-0007 - Coffee" Then
Cells(i, "A").EntireRow.Delete
End If
Next i
End Sub
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Question 1 is not clear as to your intent. The code is deleting rows based on a specific criteria, but your question implies that you want to delete an entire range regardless of what may be in column A.
To delete the entire range:
Code:
ActiveSheet.Range("A5:AC100000").Delete xlShiftUp
If you want to only delete data on rows that have "0032-0007 - Coffee"
in columns A then:
Code:
With ActiveSheet
 .Range("A4:AC100000").Autofilter 1, "0032-0007 - Coffee" 
 .Range("A5:AC100000").SpecialCells(xlCellTypeVisible).Delete
 .AutoFilterMode = False
End With
Question 2
If the imported file is the active workbook.
Code:
ActiveSheet.Range("B4")
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi JLGWhiz

Thanks for your response.

I'd like to delete a range rather than the entire rows, as there are formulae in the columns after the range that I do not want to delete.

Thanks for posting your suggestions -they're helpful - I'll try them when I'm back at work tomorrow.
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi JLGWhiz!

Thanks for your responses.

With Q2, I wanted to know how to refer to cell B4 if it wasn't the active workbook. But I'll copy that cell and put into into the active workbook, then refer to it there.

Thanks again for responding to the post.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
The simple answer is to use the parent sheet to refer to a cell or a range of cells. eg. Sheets("Sheet2").Range("B4"). But from the posted code, it is difficult to tell which sheet you are working with.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,489
Messages
5,601,986
Members
414,489
Latest member
Xlambda

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top