Clear range of cells rather than delete rows VBA

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
644
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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")
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

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
Back
Top