0

i have a counter that will identify a specific column based on the header name. that counter is the variable "k" and k=5

now in my code i need it to reference that column as my range so that i can delete that entire row where a certain criteria is met

i have 2 issues: 1. my range is not being set. I don't want to hardcode the range column 2. my loop is not running

Dim physicalstr As String
physicalstr = "Physical"
Dim physrange As Range
Set physrange = ShtTwo.Columns(k)

For Each cell In physrange
    If ActiveCell.Value = physicalstr _
    Then ActiveCell.EntireRow.Delete
Next cell
braX
  • 11,506
  • 5
  • 20
  • 33
RNJ
  • 27
  • 1
  • 5

1 Answers1

0

The immediate problems with your code are that activecell is never assigned, never changes and is not referenced in your loop (which involves cell). You don't need to activate anyway.

When deleting rows or columns you must loop backwards to avoid skipping rows. To loop backwards it's easier to use a counter variable for your loop rather than a range.

Also, do you really need to go through all million rows? Consider using Find or Autofilter for more efficient code.

Dim physicalstr As String
physicalstr = "Physical"
Dim physrange As Range
Set physrange = ShtTwo.Columns(k)
Dim r As Long

For r = physrange.Count To 1 Step -1
    If physrange.Cells(r).Value = physicalstr Then physrange.Cells(r).EntireRow.Delete
Next r
SJR
  • 22,986
  • 6
  • 18
  • 26