VBA code to "skip" empty rows

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
0
Utf
2/28/2010 5:32:01 AM
excel.programming 6508 articles. 2 followers. Follow

5 Replies
3420 Views

Similar Articles

[PageSpeed] 15

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

"Quin" <Quin@discussions.microsoft.com> skrev i meddelelsen 
news:4F428E50-F762-4F09-B7A1-DD8FF7562C4F@microsoft.com...
> 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 

0
Per
2/28/2010 6:01:45 AM
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" wrote:

> 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
0
Utf
2/28/2010 6:34:01 AM
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

0
Utf
3/1/2010 2:10:01 AM
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...

"Quin" wrote:

> 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
> 
0
Utf
3/1/2010 3:01:01 AM
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

"Quin" <Quin@discussions.microsoft.com> skrev i meddelelsen 
news:DB67DD2C-1B5A-4C5E-832A-589C6DE7BE6E@microsoft.com...
> 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
> 
0
Per
3/1/2010 8:11:37 AM
Reply:

Similar Artilces:

Undo skips a step
You would think that by now, after over 20 years of computing, I would know how Undo works, but Outlook has me flummoxed. While cleaning up my inbox and deleting entries, if I change my mind and issue the Undo command, about 75% of the time, Outlook does not restore the most recently deleted item, but one from several deletions past. What causes this?? Rick A. Pleasanton, CA ...

Copying a formula but 'skipping' a cell
I have a spreadsheet with 2 workbooks. One is an automatic data input from a GPS logger which provides a CSV file that imports a comma delimited format into discrete lines. The input comes in from this worksheet (import) as Data Set A1 Data Set B1 Data Set A2 Data Set B2 The main worksheet (main) needs to display these as Data Set A1 Data Set B1 Data Set A2 Data Set B2 I've done a sample for 10 data sets (e.g. first bit of A1 is =import!C14) but I need 1000 lines in the main worksheet. Problem is that when I copy and paste the formula it increments the cell reference by 1 and I ne...

Skipping Columns/Rows
Is it possible to skip a column or row when tabbing in a workbook? In other words, when I'm in A1 and I hit the tab key, I want to be in C1. P.S. -- I do not want to hide the columns as I'm an advocate of using the least amount of steps as necessary to produce a document. In other words, I do not want to hide, enter data, unhide, print, receive additional data, hide, enter data, unhide, print, and so on and so on. Thanks!! Lilbit, Format columns A and C to be unlocked (on the protection tab of the format dialog). Then protect the sheet, and allow selection of unlocked cells on...

How to skip the confirmation on updating link?
for updating links, under Edit > Links, I have specify that Excel always update links, and save it, when I open it next time, message box is still pop up for confirmation. Do you have any suggestions on how to skip the confirmation on updating links? Thanks in advance for any suggestions Eric What version of Excel are you using? In 2003 the option you want is Edit, Links, Startup Prompt, Don't display the alert and update links. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Eric" wrote: > for updating links, under Edi...

skipping rows with zero values
Hi there Here's my problem - Want to be able to skip rows with zero values and place next non-zero row in the one skipped. exampl B1 1 B2 B3 1 Results wante B1 1 B2 1 many thanks jojo Hi Jojo Try using Autofilter, filter column B for 0 values, and then delete those rows. HTH >-----Original Message----- >Hi there, >Here's my problem - Want to be able to skip rows with zero values and place next non-zero row in the one skipped. >example >B1 14 >B2 0 >B3 10 >Results wanted >B1 14 >B2 10 >many thanks jojo >. > Hi, I would probably s...

Skipping Zero values for plots
Hi, How do I make a chart such that if I have some zero values then those dont get plotted (especially in case of Line charts). Rather if I have values Oct 56 Nov 0 Dec 100 Then I would have only a straight line from Oct to Dec. I might have such zero value pattern for many data points. Is this possible to achieve without any complicated manipulation (I think I have one in mind). regards, HP India Two methods: 1) replace 0 (or blank) by =NA() which displays as #N?A or 2) delete 0 in data; click chart; use Tools | Options and open Chart tab; specify how missing data is to be treated bes...

repeat a formula, skipping cells
Microsoft excel. Is it possible to quickly repeat, or copy, a formula down a column skipping the same number of rows with each copy. e.g. give the total of a1..a4 in b4 and copy this so that you get a5..a8 in b8, a9..a12 in b12, etc. Hi Select B1:B4 (b1:b3 = empty and in B4 =SUM(A1:A4)) And copy the four cells down -- Regards Ron de Bruin http://www.rondebruin.nl "Hucleberry Hound" <Hucleberry Hound@discussions.microsoft.com> wrote in message news:1DB11D78-DECD-49A5-A156-60FF5613A9AF@microsoft.com... > Microsoft excel. Is it possible to quickly repeat, or copy, a f...

Skipping Sales Stage
Hello all, I am hoping to make a workflow for an opportunity that will wait for a drop down in a custom field on the opportunity record to be "yes" and then skip 1 sales stage (or change that sales stage to prospect which is the second one. thanks pat Hi pat, I am afraid thats not possible. But you can workaround if you have too. The workaround i am think of is that you will need to break the sales process into multiple sales process. For example you have one sales process with 3 stages. and on the condition you have you want to jump to the 3rd stage from the 1st. The wa...

Conditional skipping fields
I'm trying to code a data entry form where the user selects an option from a drop down list and depending on the value the cursor moves to the area pertaining to this. e.g. Choosing from animal, vegetable or mineral. If mineral is selected then the cursor bypasses the animal or vegetable data entry options and goes to the mineral choices area and proceeds. I hope this explanation is adequate. Sub forms in this instance may be a little messy for the operator. thanks Sounds suspicously like a Spreadsheet converted to Access You Should split into (at least) three tables animal, vegetabl...

numbering rows, but skipping blanks
I'm using Column A of a spreadsheet to number some items in Column B. I've been using Excel's automatic series-filling function, where you drag down from the lower right corner of a cell. However, since some of the rows in Column B are empty, and I don't want them numbered, I have to stop numbering and manually "jump" over the blanks in Column A. Is there a way to automatically number down in Column A, while skipping over the blanks and continuing where I left off? I.e. 1 2 3 4 5 ? Thanks much. Try this in A1: =IF(B1<>"",COUNTA($B$1:B1),"...

Skip Holidays
I want to calculate the settlement date(SD) base on the trade date (TD). Normanly the SD is TD +3 business days. (Skip weekends and holidays) I set up this calculation: =IF(OR(WEEKDAY(B341)=2,WEEKDAY(B341)=3),B341+3,B341+5) This works fine until there is a holiday ( eg. 7/4 and Thanksgiving) in the period. Any suggestons? Thank you. Iolani Iolani, See the NETWORKDAYS function. It will do exactly what you want. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "any" <any@any.com> wrote in mess...

skipping data for a chart
Is it possible to make a chart from a spreadsheet and not use some of the cells, i.e. data? Can you pick a range of data from cells that are not contiguous? yes "Glenn Vatter" wrote: > Is it possible to make a chart from a spreadsheet and not use some of the > cells, i.e. data? Can you pick a range of data from cells that are not > contiguous? > > > How? "bj" <bj@discussions.microsoft.com> wrote in message news:9C8C4D95-F17F-4DBB-9682-89F3001B3605@microsoft.com... > yes > > "Glenn Vatter" wrote: > >> Is it ...

"Last Channel" center button skips first number on new STB. MCE 2005 XP
The new STBs that I have use exactly the same IR commands as the ones I had before. Changing channels or going up or down is not a problem. But when I hit the center button to "return to last channel viewed" it SKIPS the first number. There must be a setting in the Regestry for this, can anyone help???? PSheaOSB ...

Sort data and have it skip everyother row when it is sorted
How can I sort my data in a column and have it a blank row between every entry First, I'd just increase the row height so that it looks like the rows are double spaced. (Inserting empty rows can screw up other things--pivottables, filters, charts... But if you want... Sort the data the way you want (we'll add the blank rows later) Find an empty column put 1 in the first row (with data) in that column Put 2 right under it. Now select those two cells and drag down the length of your data. You'll see a nice sequence of numbers--1 through the number of rows you need. Select tha...

Skipping every other data element when charting
When I am charting it is skipping every other data element/row. So for example if I have sally with 40% and then Joe with 70% and then Mary with 80% it only lists Sally and Mary on the chart but shows the bar for Joe, just doesn't list his name under the bar. Why is that? Hi, Double click the category axis and on the scale tab make sure the setting of 'Number of Categories between tick mark labels' is 1. Cheers Andy flegendre wrote: > When I am charting it is skipping every other data element/row. So for > example if I have sally with 40% and then Joe with 70% and th...

Bubble
Hi, When I make a bubble chart, xls skips the unpair data ?? It takes only the 2nd - 4th - 6th - ... values. Any idea what I do wrong ? Rgds, Fabian On Mon, 5 Nov 2007, in microsoft.public.excel.charting, Gecko <Gecko@discussions.microsoft.com> said: >When I make a bubble chart, xls skips the unpair data ?? "Unpair"? Do you mean "odd" as opposed to "even"? >It takes only the 2nd - 4th - 6th - ... values. It takes the 1st value as bubble X value (for all series), and the 2nd, 4th, and 6th values as bubble Y values. It is taking the 3rd, 5th, ...

DiscoverCard (DC) skips transactions
I see several different sounding DC problems so I am starting this one for my specific problem: Mny03 Dlx - DC Update skips transactions. My theory is that it is a DC problem related to out of date sequence transactions. By this I mean a post dated transaction, eg a new transaction with a date older than current transactions. The CD site does not support manual download of .qif files so one must manually add the missing transactions. I think this is a new problem. arthur I have the same problem. It started about April 20th and since May 7th it didn't import a single transacti...

Skip Records in a Form
I have a form in my database based on a table. The table is appended with new reocrds everyday. My goal is to have the form present only the new records and allow a user to key data into the new record and be recorded in the same table. The issue I have is that the form is displaying all records not just the new. Does anyone know a way to open a form and display only those records that have not been updated by the user? Thanks in advance Add a column for UpdateDate and update this column whenever a record is updated via the form. Set up a query to read all the rows/columns ...

outlook 2003 send sometimes skips recipients
I am supporting IT at my company. We have seen a problem with employees running outlook 2003 with exchange 2003. When employees send email to a distribution group with say numerous members bet 3 and 20 recipients all on our email server sometimes the email does not go through to all recipients. This happens when sending/forwarding email or new calendar invites. All our Outlook clients and exchange server are running the latest service packs. Has anyone seen this problem? ...

Pressing Enter to skip rows
We have a pro-forma in Excel for clients. Anyway, the cells where the enter information from a drop-down list are three rows apart, goin down the page. Is there anyway for the client to press Enter just once and go thre rows down rather than pressing enter three times? I am aware that if the sheet is locked with the particular input cell unlocked then Tab will take you to the next box however, we need Ente to do the same. I imagine a macro could achieve this. Thanks in advance D -- DoctorWar ----------------------------------------------------------------------- DoctorWard's Profile...

Navigating
Is there a way to skip a field when a yes/no box is checked? If a YES is in field A, I want to next go to field D and continue my tab order from there. If field A is not YES, then you go to field B. Thanks "DonElston" <DonElston@discussions.microsoft.com> wrote in message news:5D9A992F-9D80-49F3-88FA-5D8A51B80E7B@microsoft.com... > Is there a way to skip a field when a yes/no box is checked? > > If a YES is in field A, I want to next go to field D and continue my tab > order from there. If field A is not YES, then you go to field B. > > > Thanks...

stop cursor skipping to different place in text while typing
My cursor just skips to a different place in the text while I type. So I end up with what ever I want to write but in the middle of a previous sentence or paragraph. This is very annoying. Does anyone know how to turn this off? "Woo" <Woo@discussions.microsoft.com> wrote in message news:0A25B399-D43B-4BCF-A8CF-C74A19540D6E@microsoft.com... > My cursor just skips to a different place in the text while I type. So I > end > up with what ever I want to write but in the middle of a previous sentence > or > paragraph. This is very annoying. Does anyo...

Summing across columns while skipping some columns
Does anyone know of an easy way to accomplish this? In the example below, I do not wish to add the values in columns G18 and J18. =SUMIF(F18:L18,"<>#N/A",F18:L18) Thanks in advance for any replies. Joe How about =SUMIF(F18:L18,"<>#N/A")-SUMIF(G18,"<>#N/A")-SUMIF(J18,"<>#N/A") Note the third argument is not needed since you are summing the same range as you are testing. best wishes -- Bernard Liengme Microsoft Excel MVP people.stfx.ca/bliengme email address: remove uppercase characters "JAG" <JA...

Win7 backup error: Windows Backup skipped I:\ because it cannot be found on drive I:\
After expanding my O/S disk and thus deleting "Disk I" my windows 7 backup completes successfully but gives me the above error for the previously deleted Disk. More of an annoyance. I DID have a disk I on the first Backup file I did before the expansion and I have NOT tried to restore any files, etc yet. I am looking for a registry or ? change that can fix this and not necessarily a 'work-around.' My Computer, Device Manager, Win 7 Backup devices, etc do NOT show any "Disk I." available on the computer. All help is appreciated. Backup error: Window...

Skip condition if cell is blank
I have 4 conditions 1) If Sheets("Details").Range("H" & lngRow) > Date - 90 And _ 2) Sheets("Details").Range("F" & lngRow) = Sheets("Search").Range("C2") And _ 3) Sheets("Details").Range("J" & lngRow) = Sheets("Search").Range("C4") And _ 4) Sheets("Details").Range("I" & lngRow) = Sheets("Search").Range("E4") Then if Sheets("Search").Range("E2") is blank then condition 1 should be skipped if Sheets("Sear...