Trying to convert a used range into a current region

[a]

Hi, I was hoping that somebody could help me with this.

I have non-continguous ranges and I want to copy the formula down to the
bottom of the column "at" but the rows in between the non-contiguous
ranges prevent this because the intersection is using the current region
rather than the used range. Is there a way to do use the intersection
on the used range instead the current region? As you can see - I try to
convert the current region into the used range - but that clearly didn't
work.

Thanks much in advance for any help you can provide.

anita

Sub EngDeleteRows()
Dim lastRow As Long
Dim rng As Range

Application.ScreenUpdating = False
'insert dummy field name
ActiveSheet.Unprotect password:="nope"
Range("at13").Value = "temp"
Range("at14").Formula = "=RC[-17]+RC[-5]+RC[-1]"
Range("at14").Activate
ActiveSheet.UsedRange.Select
CurrentRegion = ActiveSheet.UsedRange 'my lame attempt
Range("at14").Activate
Intersect(Range(ActiveCell, _
Cells(Rows.Count, ActiveCell.Column)), _
ActiveCell.CurrentRegion).FillDown
With ActiveSheet
.UsedRange 'reset last cell
'determine last row
lastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
'set rng to the B column data rows
Set rng = Range("at14", Cells(lastRow, "at"))
'filter the B column to show only the data to be deleted
rng.AutoFilter Field:=1, Criteria1:="0", Operator:=xlOr, _
Criteria2:="="

'delete the visible cells, including dummy field name
rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.UsedRange 'reset the last cell
Range("a1").Select
End With
End Sub

 

[Tom Ogilvy]

Intersect(Range(ActiveCell, _
Cells(Rows.Count, ActiveCell.Column)), _
ActiveCell.Parent.UsedRange).FillDown

 

[a]

Thank you - Tom Ogilvy!

I haven't tried this yet - it's my bedtime - but your stuff always works
and I've already sent it to work.

Thanks again,
Anita

Intersect(Range(ActiveCell, _
Cells(Rows.Count, ActiveCell.Column)), _
ActiveCell.Parent.UsedRange).FillDown


--
Regards,
Tom Ogilvy

Hi, I was hoping that somebody could help me with this.

I have non-continguous ranges and I want to copy the formula down to the
bottom of the column "at" but the rows in between the non-contiguous
ranges prevent this because the intersection is using the current region
rather than the used range. Is there a way to do use the intersection
on the used range instead the current region? As you can see - I try to
convert the current region into the used range - but that clearly didn't
work.

Thanks much in advance for any help you can provide.

anita

Sub EngDeleteRows()
Dim lastRow As Long
Dim rng As Range

Application.ScreenUpdating = False
'insert dummy field name
ActiveSheet.Unprotect password:="nope"
Range("at13").Value = "temp"
Range("at14").Formula = "=RC[-17]+RC[-5]+RC[-1]"
Range("at14").Activate
ActiveSheet.UsedRange.Select
CurrentRegion = ActiveSheet.UsedRange 'my lame attempt
Range("at14").Activate
Intersect(Range(ActiveCell, _
Cells(Rows.Count, ActiveCell.Column)), _
ActiveCell.CurrentRegion).FillDown
With ActiveSheet
.UsedRange 'reset last cell
'determine last row
lastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
'set rng to the B column data rows
Set rng = Range("at14", Cells(lastRow, "at"))
'filter the B column to show only the data to be deleted
rng.AutoFilter Field:=1, Criteria1:="0", Operator:=xlOr, _
Criteria2:="="

'delete the visible cells, including dummy field name
rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.UsedRange 'reset the last cell
Range("a1").Select
End With
End Sub