Help Moving data down 1 row

[srdear]

Hi there.... I'm new to this forum so be gentle.

I have a Rental Form that i'm working on, and to tidy things up when I print it out, I would like to move a cells data down 1 row if that cell is empty. Here's what I have:
cell b8 - First and Last Name
cell b9 - Address1
cell B10 - Address2
cell b11 - City, State, Zip
cell 12 - Phone #
etc.

Not too many people have an address2, so when that field is empty, I'd like to move Address1's data down to it's positon (address2). If they do, and address2 has data in it, leave address1 where it is. Simple right? This moving would happen when the command button is hit and the form data goes to sheet 1 which works fine.
Any help would be great!
Thanks in advance

Steve

 

[Redwolfx]

You may want to start by posting your existing code. The move portion of the macro will need to be integrated into that code.

 

[srdear]

Below is the vb code I'm using in the form.



Private Sub CommandButtonNext_Click()
'Name
Range("B8").Value = TextBox1.Value
'Address1
Range("B9").Value = TextBox2.Value
'address2
Range("B10").Value = TextBox3.Value
'combines city,state,zip,textboxes data to 1 cell with spaces & comma
Range("B11").Value = TextBox4.Value & ", " & TextBox5.Value & " " & TextBox6.Value
'phone
Range("B12").Value = TextBox7.Value
'dl#
Range("B13").Value = TextBox8.Value

Unload UserForm1
UserForm2.Show
End Sub

Private Sub TextBox7_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'formats phone number as its entered
Dim Char As Long
Dim I As Long
Dim N As Long

If KeyAscii < 48 Or KeyAscii > 57 Then
KeyAscii = 0
Else
For I = 1 To Len(TextBox7.Text)
Char = Asc(Mid(TextBox7.Text, I, 1))
If Char >= 48 And Char <= 57 Then N = N + 1
Next I

Select Case N
Case 3
If Right(TextBox7.Text, 1) <> "-" Then TextBox7.Value = TextBox7.Value & ")" & " "
If Left(TextBox7.Text, 1) <> "(" Then TextBox7.Value = "(" & TextBox7.Value
Case 6
If Right(TextBox7.Text, 1) <> "-" Then TextBox7.Value = TextBox7.Value & "-"
Case 9
If Left(TextBox7.Text, 1) <> "(" Then TextBox7.Value = "(" & TextBox7.Value
If Mid(TextBox7.Text, 5, 1) = "-" Then
TextBox7.Value = Left(TextBox7.Value, 4) & ")" & Mid(TextBox7.Text, 6, 8)
End If
End Select
End If


End Sub

Private Sub CommandButtonClear_Click()
'clears all fields
Dim z As Control
For Each z In UserForm1.Controls
If TypeName(z) = "TextBox" Then
z.Value = ""
End If
Next z
End Sub