Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to convert a used range into a current region
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.Dele te .UsedRange 'reset the last cell Range("a1").Select End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to convert a used range into a current region
Intersect(Range(ActiveCell, _
Cells(Rows.Count, ActiveCell.Column)), _ ActiveCell.Parent.UsedRange).FillDown -- Regards, Tom Ogilvy a wrote in message nk.net... 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.Dele te .UsedRange 'reset the last cell Range("a1").Select End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Thank you Tom Ogilvy
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 Tom Ogilvy wrote: Intersect(Range(ActiveCell, _ Cells(Rows.Count, ActiveCell.Column)), _ ActiveCell.Parent.UsedRange).FillDown -- Regards, Tom Ogilvy a wrote in message nk.net... 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.Dele te .UsedRange 'reset the last cell Range("a1").Select End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Current Region..Cntrl Asterick | Excel Discussion (Misc queries) | |||
convert existing range of dates to to current year, maintaining mm | New Users to Excel | |||
Current region emulation | Excel Worksheet Functions | |||
Paste values all cells in a region which refers to out of region | Excel Worksheet Functions | |||
Sort doesn't recognize current region | Excel Worksheet Functions |