VB Question

[earthsuiter]

Good Afternoon,

I'm tring to figure out what the VB code I would need to do the following:

Look at all values in column B
If value = "break" then .RowHeight = 2.5

Thanks for your time.
Tim

 

[VoG]

I'm not clear what you want to do but maybe look at the Select Case statement

 

[dave3009]

Select Case option

Sub jhj()
Dim LR, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    Select Case Range("B" & i).Value
        Case Is = "break"
            Rows(i).RowHeight = 2.5
        Case Is = "space"
            Rows(i).RowHeight = 15
        Case Is = "header"
            Sheets("Coding").Range("A1:E1").Copy Range("B" & i)
    End Select
Next i
End Sub

 

[earthsuiter]

Awesome ...I'm pretty sure this is my last question.
How do you code it to look for the value '@' and insert x number of blanks rows down?

 

[VoG]

Try

Sub jhj()
Dim LR, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    Select Case Range("B" & i).Value
        Case "break": Rows(i).RowHeight = 2.5
        Case "space": Rows(i).RowHeight = 15
        Case "header": Sheets("Coding").Range("A1:E1").Copy Range("B" & i)
        Case "@": Rows(i).Resize(3).Insert
    End Select
Next i
End Sub

 

[earthsuiter]

Hi Peter,

It works, but... Not sure.

Is there a way to tell the Macro, as soon as you see the '@' stop and put the remainder of data on A1 of page 2?

 

[VoG]

Perhaps

Sub jhj()
Dim LR, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    Select Case Range("B" & i).Value
        Case "break": Rows(i).RowHeight = 2.5
        Case "space": Rows(i).RowHeight = 15
        Case "header": Sheets("Coding").Range("A1:E1").Copy Range("B" & i)
        Case "@": Rows(i & ":" & LR).Cut Destination:=Sheets("Sheet2").Range("A1"): Exit Sub
    End Select
Next i
End Sub

 

[earthsuiter]

Peter,

With the last change you made to the Macro, it is only formatting the data from A76 down. I still need the Macro to format everything on the page. Thanks again for all your help.

Case "@": Rows(i & ":" & LR).Cut Destination:=Sheets("Test Sheet").Range("A76"): Exit Sub

 

[VoG]

I edited my code - try with the new code.

 

[earthsuiter]

Hi Peter,

I must have missed something, I don't see any new code.

 

[VoG]

The revised code that I posted here http://www.mrexcel.com/forum/showpost.php?p=1999868&postcount=16