Excel macro to apply format to penultimate row of selected range

0
votes

I am using a macro to add formatting to a selected range of cells. (Excel 2007) The number of columns and rows of the selected range are always different. Also the selected areas are not always on the same worksheet.

I recorded a macro and made some minor changes to the code, but I was unable to figure out how I could apply formatting to the cells of the penultimate row of the selected range, which would be in this case double underline borders.

Sub feladat()

Application.ScreenUpdating = False

Application.CutCopyMode = False
With Selection
    .HorizontalAlignment = xlCenter
End With

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
    .Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
    .Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
    .Weight = xlMedium
End With
With Selection.Borders(xlInsideVertical)
    .Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
    .Weight = xlThin
End With

Selection.SpecialCells(xlCellTypeFormulas, 23).Select
Selection.Font.Bold = True

Application.ScreenUpdating = True

End Sub

I've tried finding the penultimate row using offset, but with no luck since after running the macro the active cell is always located at a different place.

2
excelvba

2 Answers

0
votes

First of all, just some advice: if you really need to apply your format on the selection, it'ok to use .Select. But first thing you should learn on VBA: Selections and Activations are to be avoided in order to write a good and efficient code.

Anyway to obtain a selection of the penultimate row in a selected range, you can use a simple pair of commands:

TheRow = Selection.Rows.Count-1 + Selection(1).Row
With Workbooks("NameOfYourWorkbook").Worksheets("NameOfYourWorksheet").Rows(TheRow)
    ' Your formatting here WITHOUT writing Selection
    ' For example: .Borders(xlDiagonalDown).LineStyle = xlNone
End With

This will get the number of rows in your selection and subtract 1. Then add the row of the first cell, so you get a clear absolute row address.
Done that, you can apply your formatting. This doesn't need new selections too.

0
votes

Try This to find and apply format to Penultimate row Before running your macro select the range. Edit 

Sub NewMacro()
ShtNm = ActiveSheet.Name
Var = Split(Selection.Address, "$")
 Rwcnt = Var(UBound(Var))
 NewRng = Var(1) & (Rwcnt-1) & ":" & Var(3) & (Rwcnt-1)
Sheets(ShtNm).Range(NewRng).Select 
Call feladat 'Call your function
End Sub