Lastrow of a range
Hi all, I have a macro which deletes rows in a report with multiple borders
where it deletes blank rows to compact the print area. Unfortunately one of the cells loses its bottom border when the row above is deleted. The cell is the last cell in the range V2:V20 and there is a similar range below it in row V. The number of rows in the range after deletion is dynamic so I cannot use an absolute reference. I have added the code I have tried below but have run out of ideas. Any help would be appreciated. Sub filterrows1() 'Dim myrange As Range Cells.Select With Selection Rows("2:200").Select .AutoFilter Field:=30, Criteria1:="1" Selection.EntireRow.Delete Shift:=xlUp ActiveSheet.ShowAllData ' Set myrange = Range("V2:V20") ' myrange.Lastrow.Select Range(Cells(firstrow, firstcolumn), Cells(Lastrow, lastcolumn)).Select ' LastRow = .Cells(Range("V2:V20").End(xlUp)) With Selection.FormatConditions(1).Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Thanks Davie |
Lastrow of a range
try this.
from With Selection.FormatConditions(1).Borders(xlBottom) to With Selection.FormatConditions(1).Borders(xlInsideHori zontal) "davethewelder" wrote: Hi all, I have a macro which deletes rows in a report with multiple borders where it deletes blank rows to compact the print area. Unfortunately one of the cells loses its bottom border when the row above is deleted. The cell is the last cell in the range V2:V20 and there is a similar range below it in row V. The number of rows in the range after deletion is dynamic so I cannot use an absolute reference. I have added the code I have tried below but have run out of ideas. Any help would be appreciated. Sub filterrows1() 'Dim myrange As Range Cells.Select With Selection Rows("2:200").Select .AutoFilter Field:=30, Criteria1:="1" Selection.EntireRow.Delete Shift:=xlUp ActiveSheet.ShowAllData ' Set myrange = Range("V2:V20") ' myrange.Lastrow.Select Range(Cells(firstrow, firstcolumn), Cells(Lastrow, lastcolumn)).Select ' LastRow = .Cells(Range("V2:V20").End(xlUp)) With Selection.FormatConditions(1).Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Thanks Davie |
Lastrow of a range
Maybe this will help get what you want:
Sub test() Dim LRow As Long 'last row on sheet in column V LRow = Cells(Rows.Count, "V").End(xlUp).Row 'last row of contiguous data in column V 'starting with cell V2 LRow = Range("V2").End(xlDown).Row End Sub Mike F "davethewelder" wrote in message ... Hi all, I have a macro which deletes rows in a report with multiple borders where it deletes blank rows to compact the print area. Unfortunately one of the cells loses its bottom border when the row above is deleted. The cell is the last cell in the range V2:V20 and there is a similar range below it in row V. The number of rows in the range after deletion is dynamic so I cannot use an absolute reference. I have added the code I have tried below but have run out of ideas. Any help would be appreciated. Sub filterrows1() 'Dim myrange As Range Cells.Select With Selection Rows("2:200").Select .AutoFilter Field:=30, Criteria1:="1" Selection.EntireRow.Delete Shift:=xlUp ActiveSheet.ShowAllData ' Set myrange = Range("V2:V20") ' myrange.Lastrow.Select Range(Cells(firstrow, firstcolumn), Cells(Lastrow, lastcolumn)).Select ' LastRow = .Cells(Range("V2:V20").End(xlUp)) With Selection.FormatConditions(1).Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Thanks Davie |
Lastrow of a range
Mike, Joel, I used Mike's code for the end of the ranges and I changed the
border to read "xlInsideHorizontal" as you suggested Joel but I now get an error "Unable to set the linestyle of the Border Class". The linestyle is set to xlContinuous The .Weight is set to xlthin and the .ColorIndex is set to xlAutomatic. Do i need to change these? Davie "Mike Fogleman" wrote: Maybe this will help get what you want: Sub test() Dim LRow As Long 'last row on sheet in column V LRow = Cells(Rows.Count, "V").End(xlUp).Row 'last row of contiguous data in column V 'starting with cell V2 LRow = Range("V2").End(xlDown).Row End Sub Mike F "davethewelder" wrote in message ... Hi all, I have a macro which deletes rows in a report with multiple borders where it deletes blank rows to compact the print area. Unfortunately one of the cells loses its bottom border when the row above is deleted. The cell is the last cell in the range V2:V20 and there is a similar range below it in row V. The number of rows in the range after deletion is dynamic so I cannot use an absolute reference. I have added the code I have tried below but have run out of ideas. Any help would be appreciated. Sub filterrows1() 'Dim myrange As Range Cells.Select With Selection Rows("2:200").Select .AutoFilter Field:=30, Criteria1:="1" Selection.EntireRow.Delete Shift:=xlUp ActiveSheet.ShowAllData ' Set myrange = Range("V2:V20") ' myrange.Lastrow.Select Range(Cells(firstrow, firstcolumn), Cells(Lastrow, lastcolumn)).Select ' LastRow = .Cells(Range("V2:V20").End(xlUp)) With Selection.FormatConditions(1).Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Thanks Davie |
All times are GMT +1. The time now is 07:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com