Category Archives: Excel

Sub ColumnToDateTime()
'
' ColumnToDateTime Macro
'

'
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "yyyy-mm-dd hh:mm:ss"
End Sub



Sub AddBorders()
'
' AddBorders Macro
'

'
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End Sub

Add buttons.

For cell K1 copy value every 30 rows down.

=IF(MOD(ROW(),30)=1,$K$1,"")

If you want to extend your range down you can do this by calculating the first 2 cells with your formula and then highlighting them. You now grap the little square on the bottom right of the highlighted area and drag that down, across or both.

If you have a specific cell (e.g. D2) which you wish for to remain in all the cells you extend your range to then in your 2 initial cell calculations use the following:

Exntending Down

=G2*D$2
=G3*D$2
Extending Across

=G2*$D2
=H2*$D2
Extending Down and Across

Use $D$2 

Here’s the original source.