VB Question

earthsuiter

New Member
Joined
Jul 14, 2009
Messages
34
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Hi Tim

Welcome to the Board

Code:
Sub mnm()
Dim lst As Long
Dim my, c As Range
lst = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
Set my = ActiveSheet.Range("B2:B" & lst)
    For Each c In my
        If c.Value = "break" Then c.RowHeight = 2.5
    Next c
End Sub
 
Upvote 0
Hello and welcome to MrExcel.

Try

Code:
Sub test()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("B" & i).Value = "break" Then Rows(i).RowHeight = 2.5
Next i
End Sub
 
Upvote 0
Thanks for your help Dave and Peter. That worked just like I needed it to.
One last question along the same line...

Look at all values in column B
If value = "header" go to sheet 2 copy cells a1, b1, c1, d1, e1
and paste them on sheet 1 cells a1, b1, c1, d1, e1

Thanks again,
Tim
 
Upvote 0
Try

Code:
Sub test()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("B" & i).Value = "break" Then
        Rows(i).RowHeight = 2.5
    ElseIf Range("B" & i).Value = "header" Then
        Sheets("Sheet2").Range("A1:E1").Copy Destination:=Range("A1")
    End If
Next i
End Sub
 
Upvote 0
Hi Peter,

Thanks once again.
I do have a follow up question because the word header appears more than once in my import file.

Is there anyway to tell Excel, no matter where you see the word "header" copy and paste from sheet 2 a1, b1, c1, d1, e1

Thanks,
Tim
 
Upvote 0
The code will do that but always into row 1 of the active sheet. Should it be pasted into the row where "header" is found?
 
Upvote 0
Try

Code:
Sub test()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("B" & i).Value = "break" Then
        Rows(i).RowHeight = 2.5
    ElseIf Range("B" & i).Value = "header" Then
        Sheets("Sheet2").Range("A1:E1").Copy Destination:=Range("A" & i)
    End If
Next i
End Sub
 
Upvote 0
What do I need to put if I need to do this for several different words as far as the If, and ElseIf commands?

Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If Range("A" & i).Value = "break" Then
Rows(i).RowHeight = 2.5
If Range("A" & i).Value = "space" Then
Rows(i).RowHeight = 15#
ElseIf Range("A" & i).Value = "header" Then
Sheets("Coding").Range("A1:E1").Copy Destination:=Range("B" & i)
End If
Next i
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top