VBA code to "skip" empty rows

[Quin]

This code will take text from column A in an Excel Work sheet and insert it
into column B inside a text box. I would like to “tweek†this to skip over
the code that creates the text boxes on each row that contains empty text.
The result I seek is to have a text box created with text copied from column
A and no text box created when column A is empty.

My attempt to do this myself is not working out. I am just starting to
learn something about VBA. Any comments for a better approach to solve this
would be appreciated. I have commented out my attempt to skip past the
textbox creation code until it can be fixed.
Quin


Option Explicit

Sub MakeTextboxes()


Dim iLeft As Long
Dim iTop As Long
Dim iWidth As Long
Dim iHeight As Long
Dim iRow As Long

iLeft = Range("B1").Left
iWidth = Range("B1").Width

For iRow = 1 To 5
' If Cells(1, iRow).Value = "" Then GoTo Skip


iTop = Range("b" & iRow).Top
iHeight = Range("b" & iRow).Height

With ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, _
iLeft, iTop, iWidth, iHeight)

.TextFrame.Characters.Text = Range("a" & iRow)
.TextFrame.MarginBottom = 0
.TextFrame.MarginLeft = 0
.TextFrame.MarginRight = 0
.TextFrame.MarginTop = 0


End With
'Skip:

Next iRow

End Sub

 

[Per Jessen]

Hi Quin

The syntax for the Cells statement is :

Cells(Row, Column)

Also I always try to avoid goto statements, unless it is the only option:


Sub MakeTextboxes()
Dim iLeft As Long
Dim iTop As Long
Dim iWidth As Long
Dim iHeight As Long
Dim iRow As Long

iLeft = Range("B1").Left
iWidth = Range("B1").Width

For iRow = 1 To 5
If Trim(Cells(iRow, 1).Value) <> "" Then
iTop = Range("b" & iRow).Top
iHeight = Range("b" & iRow).Height

With ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, _
iLeft, iTop, iWidth, iHeight)

.TextFrame.Characters.Text = Range("a" & iRow)
'.TextFrame.MarginBottom = 0
'.TextFrame.MarginLeft = 0
'.TextFrame.MarginRight = 0
'.TextFrame.MarginTop = 0
End With
End If
Next iRow

End Sub

Regards,
Per

 

[Mike H]

Hi,

Try this

Sub MakeTextboxes()
Dim iLeft As Long
Dim iTop As Long
Dim iWidth As Long
Dim iHeight As Long
Dim iRow As Long
iLeft = Range("B1").Left
iWidth = Range("B1").Width
For iRow = 1 To 5
If Range("A" & iRow).Value = "" Then GoTo Skip
iTop = Range("b" & iRow).Top
iHeight = Range("b" & iRow).Height
With ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, _
iLeft, iTop, iWidth, iHeight)
.TextFrame.Characters.Text = Range("a" & iRow)
.TextFrame.MarginBottom = 0
.TextFrame.MarginLeft = 0
.TextFrame.MarginRight = 0
.TextFrame.MarginTop = 0
End With
Skip:
Next iRow
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Quin]

Mike H,

Thanks for fixing my code. I can see how the “Range†statement can be used
to accomplish my task. The result is exactly what I needed and wanted.


Jessen,

I found a lot of interesting information in your reply, but since I am
learning, I also have several questions. I hope you have a minute to answer.


1. I don’t understand why it is frowned upon to use goto statements. It
seems like such an easy and straight forward thing to do.
2. I can’t get my mind around using a <> symbol. I tried to Google it with
no success.
3. I Googled “Trim†to find it removes leading and trailing spaces but not
sure why that helps in this case.
4. I’m not sure why the text frame section is commented. Is that just to
use default settings when the text boxes are created?

My last observation is “I must be Dyslexic†Very poor of me to not notice my
confusion on the use of the “Cells†statement. I appreciate your comment to
direct my attention to it.
Thank you Mike and Jessen

Quin

 

[Quin]

I just figured out what "Trim" does. It eliminates "spaces" in cells that
would otherwise create an unwanted text box. I will test that out...

 

[Per Jessen]

Quin,

Thanks for your reply.

1. I understand that you find it straight forward to use Goto, but when you
start writing larger applications, you will find it harder to follow the
structure in the code, specially if your code need more than one goto
statement. I only use goto statements in error handling situations.

2. '<>' (not equal to) is the opposite of '=' (equal to). So if the cell
tested is not empty, then proceed with next statement, else goto end if
statement.

3. Glad you figured out why I inserted the Trim statement.

4. The textbox margin section is commented out, because they are only needed
if you want to change default settings.

I hope this answered the questions.

Per