Increase Row Height of every other line

  • Thread starter Thread starter waddsn1060
  • Start date Start date
W

waddsn1060

I am trying to increase the row height of every other line through a
macro. I already was able to do shading and bolding through conditional
formatting but that does not allow me to change row height. The
document in close to a 400 rows long and dont want to highlight each
row. I already have the following code to change other settings in the
document.

Sub Cleanup()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim R As Long
Dim rng As Range
numRows = 1
Set rng = ActiveSheet.UsedRange
For R = rng.Rows.Count To 1 Step -1
rng.Rows(R + 1).Resize(numRows).EntireRow.Insert
Next R
Application.ScreenUpdating = True

Range("A2").Select
ActiveCell.FormulaR1C1 = "=REPLACE(,1,7,R[1]C[8])"
Selection.Copy
Columns("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
ActiveSheet.Paste
Range("A2").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=REPLACE(,1,7,R[1]C[8])"
Selection.Copy
Columns("B:B").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
ActiveSheet.Paste
Range("B2").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = "=REPLACE(,1,7,R[1]C[8])"
Selection.Copy
Columns("C:C").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
ActiveSheet.Paste
Range("C2").Select

Range("A1:K1").Select
With Selection.Font
.Name = "Arial"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
Range("A1:K350").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)=0"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
End With
With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.FormatConditions(1).Interior.ColorIndex = 15
End Sub
 
This will do everyother row


Sub RowHeight()

For x = 1 To 400

Rows(x & ":" & x).RowHeight = 37.5
x = x + 1

Next x

End Sub

(e-mail address removed)
 
Hi,

you may slightly modify your code (I am posting only start of the
code):

Sub Cleanup()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim R As Long
Dim rng As Range
numRows = 1
Set rng = ActiveSheet.UsedRange
With rng
For R = .Rows.Count To 1 Step -1

With .Rows(R + 1).Resize(numRows).EntireRow
.Insert
.RowHeight = 20 'here is the height of row, change it if you need
End With
Next R
End With
Application.ScreenUpdating = True

Regards,
Ivan
 
Back
Top