• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA help - skipping and picking up after blank rows

M

maku

New Member
I have a VBA script I use to consolidate data from multiple tabs in my workbook. Each tab has identical columns of data headers. The YEAR data header starts on column "Q". I thought the VBA script was working great, but noticed a flaw. I want to have it set that if I type a value in the row under YEAR, "ie- 2012", then the rest of that line is copied and pasted to my Master Data tab, or consolidated tab. If the YEAR cell is empty, to skip that line, BUT I want it to continue down.


The problem I'm having, is the script seems to stop looking for rows that have a value in the cell YEAR once it hits a blank cell on that tab, and then it continues to the next tab. If I don't have any "blank" entries, it works fine. Here's a look below:

[pre]
Code: 
Q (YEAR)  R (PERIOD)  S (SALES)
1    2012      1st Qtr     1000
2    2012      2nd Qtr     1000
3              July        1000
This would correctly copy lines 1 and 2, but skip line 3, as nothing is entered in the YEAR.

[pre][code]Q (YEAR)  R (PERIOD)  S (SALES)
1              1st Qtr     1000
2    2012      2nd Qtr     1000
3    2012      July        1000
[/pre]
This is where the problem occurs, since Line 1, cell Q is empty, it not only skips that line, it moves to the next tab. I want the script to copy Lines 2 and Line 3 because the year has a value filled in. Hopefully I gave enough info. Thanks for any help. Code is below. Thanks! -Maku

Option Explicit
Dim wsMain As Worksheet
Dim ws As Worksheet
Dim FValue As String
Dim i As Long
Dim lrow As Long

Sub ConsolidateMasterData()

Application.ScreenUpdating = False

FValue = "Year"

Set wsMain = Worksheets("Master Data")
wsMain.Cells.Clear

lrow = Worksheets("Master Data").Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To Worksheets.Count
With Worksheets(i)
If .Name <> "Master Data" Then
lrow = .Range("Q" & .Rows.Count).End(xlUp).Row
If .Range("Q2").Value <> "" Then
If .Range("Q1") = FValue Then
.Range("Q2:DZ" & lrow).Copy
Worksheets("Master Data").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
End If
End If
End With
Next i[/code][/pre]
 
I'm "guessing" that you can change this line

[pre]
Code: 
If .Range("Q2").Value <> "" Then
Which is probably there to make sure there's some data, to this:

If lrow >= 2 Then
[/pre]
Since the code has already found the lastrow in col Q, you really just need to make sure that there's more than 1 row of data to be copied.
 
<p> Obviously, this may be revealed within the newsletters. Among the much more critical information which have been composed already.</p>


<p>Look Hot and Stunning With Karen Millen</p>


<p> To show the affiliate is operating efficiently and effectively, inquiries would need to be paid much more interest on. No one desires to be ignored and consumers will not be usually probably the most affected person of all people. Speedy answer that ought to appear expert however friendly too.</p>


<p> </p>


<p> The newsletters and ezines had been updated days back, so it truly is time for the affiliate marketer to determine if there are some new factors occurring inside the industry. This can be published about within the marketer's publication to become distributed towards the old and new consumers.</p>


<div class="sideBoxContentItem"><img title=" Karen Millen Outlet Feminine Textured Tailor Dress Black/White " alt="Karen Millen Outlet Feminine Textured Tailor Dress Black/White" src="http://www.karen-millen-outlets.co.uk/bmz_cache/f/fa83b449c276eb81b24c528147c897e7.image.128x200.jpg" width="128" height="200" style="position: relative" />

Karen Millen Outlet Feminine Textured Tailor Dress Black/White</div>


<div class="ProductImage"><img title=" Karen Millen Outlet Snake Print Pencil Dress " alt="Karen Millen Outlet Snake Print Pencil Dress" src="http://www.karen-millen-outlets.co.uk/bmz_cache/3/3125f034ecffb1a900a46376fce59802.image.125x200.jpg" width="125" height="200" style="position: relative" /></div>

<div class="ProductDetails">Karen Millen Outlet Snake Print Pencil Dress</div>


<p> Large range of <span style="color: #ff6600">Karen Millen Dresses for sale</span> on the internet now from Karen Millen United kingdom Sale Store.</p>


<p> The site style has to become revised. The marketer knows that a well-designed website can improve indication ups from guests. It may also aid inside the affiliate's conversion rates.</p>


<p> Within the method of doing all the requirements, the marketer is logged on to a chat room exactly where he or she interacts with other affiliates and people beneath that identical plan. This can be exactly where they are able to talk about factors on the way to ideal promote their goods.</p>

<p>If you are planning for a hot romantic date or fun filled party with your friends, you can 'fabulize' your feet wearing a pair of Karen Millen shoes. You can snuggle into a pair of sneaky and funky looking leather flat shoes by Karen Millen and look sparklingly beautiful. The shoes are trimmed with toe bows and some other sparkling embellishments. When you walk on these shoes, your feet will shine like twinkling stars and all heads will be turned towards you.The hottest brand this summer is Karen Millen. This brand is favoured by fashion conscious and style lover people coming from every strata of life. Right from the ordinary next-door girl to models, celebrities, fashion magazine editors, singers, and this brand has rocked the glamour world. There is no reason for you to be an exception. You can also stun everyone in your social circle by owning a pair of <span style="color: #ff6600">Karen Millen outlet</span>. You will surely look classy and sophisticated with a Karen Millen footwear or attire.</p>

<p> </p>


<p> Time to track down the product sales you will be obtaining from your affiliates fairly and precisely. You can find telephone orders and mails to track down. See if they are new clients checking the goods out. Noting down the contact info that could be a practical source within the long term.</p>


<p> You'll find lots of resources to sort out. Ads, banners, button advertisements and sample suggestions to offer out simply because the marketer knows that this is one particular way of ensuring much more revenue. Best to stay noticeable and obtainable as well.</p>


<p> It's that point to show some appreciation to individuals who have aided the marketer inside the promotions and sale increase. Nothing at all like mentioning the persons, their internet sites and also the procedure they have completed that produced almost everything worked.</p>


<p> Is that good results looming within the length or what</p>


<p>You'll find factors to be realized and it is a constant process. Sharing ideas and advices is actually an excellent method of displaying help. There could be others Karen Millen Dresses around seeking to join and may be enticed by the talk which is going on. There is no harm in assuming what possibilities ahead.</p>


<p>welcome to www.karen-millen-outlets.co.uk/</p>


<div class="sideBoxContentItem"><img title=" Karen Millen Outlet Crystal Jewel Print Dress Multicolour " alt="Karen Millen Outlet Crystal Jewel Print Dress Multicolour" src="http://www.karen-millen-outlets.co.uk/bmz_cache/9/94031340df6680a02c6ed1bd1c1f6492.image.128x200.jpg" width="128" height="200" style="position: relative" />

Karen Millen Outlet Crystal Jewel Print Dress Multicolour</div>

<div class="sideBoxContentItem">The affiliate marketer remembered that you can find concerns to answer in the visitors. It has to be <span style="color: #ff6600">Karen Millen Dress</span> completed quickly. Nothing can turn off a consumer than an unanswered email.</div>


<p> .</p>


<p> These identical publications can also be an crucial tool in trying to keep up to date with all the freshly released products. The marketer has place up a sale and advertising that buyers may desire to learn about. In addition to, they have to help keep up with the deadline of those product sales composed inside the publications.</p>


<p> That accomplished, it truly is time for you to submit the affiliate program to directories that lists affiliate programs. These directories are implies to attract individuals in becoming a member of your affiliate program. A certain means of promoting the affiliate plan</p>


<p> Okay, so this may not be all carried out inside a day. But then, this gives you an thought of how an affiliate marketer, a dedicated 1 which is, spends the advertising and marketing day.</p>


<p> So with technologies at hand, and assuming that the affiliate is operating from residence, per day in his or her lifestyle would audio a thing such as this</p>

<p> On waking up and soon after having breakfast, the personal computer is turned on to take a look at Karen Millen Dresses new developments in the network. As much because the marketer is concerned there could be new factors to update and statistics to keep monitor on.</p>

<p> The marketer nevertheless has time to write out recommendations to those who want credible resources for your merchandise becoming promoted. There is certainly also time for you to article some comments on how to be described as an effective affiliate marketer over a web site where you'll find a great deal of wannabees.</p>


<p>Being inside the affiliate marketing company is not that difficult now using the net at your disposable. It is much simpler now in comparison with Karen Millen Dresses the days when people need to make use in the telephones and other mediums of info just to obtain the latest updates around the way their system is coming alongside.</p>


<p>The clothing items are available in vibrant and flamboyant colours. The designs are exclusive and innovative ones and speak of high craftsmanship. The shoes are of different types to suit every mood. The range of these items also comes at different prices, so that it can suit all types of pockets. You don't have to worry about spending a huge amount of money to augment your closet. This brand guarantees to make you look stunning and glamorous. The clothes can be worn on any occasion, whether it is formal or casual one. Wherever you go in a dress or shoes of this brand, you will surely steal the show.Though this popular brand is available in all the leading stores all across the world, you can check out the latest collection from any online retailer at your own convenient time. Those who are modern and fashionista people, stun the onlookers by wearing the designs of styles that have not yet come to the fashion magazines. They can do so only through internet shopping. Online shopping lets you take advantage of attractive deals and discounts. You need to just log onto any reliable website and browse through the extensive collection of different items under this brand. The footwear and apparel are available in a wide range of styles, designs and colours. After you make the selection, you have to mention the size, colour or design you prefer in an online form. Then you have to make an online payment through your bank ATM or credit cards. Once the order is placed, you will be sure of receiving the products at your doorway within a very few days. Some online retailers charge a nominal fee towards shipping, but the amount is worth that saves your time, money and energy, in comparison with traditional shopping. </p>


<p> Time flies. Missed lunch but is very contented with all the tasks accomplished. Bed time .</p>


<p> Two objectives accomplished <span style="color: #ff6600">Karen Millen Dresses</span> at the exact same time. The marketer gets to advertise the product at the same time because the program they're in. Who understands, someone could be inclined to join.</p>Related articles：

 
Luke...thanks for the idea...it sort of worked. it did pick up the rows after the blank rows, but it also picked up the blank rows, or rows that had a blank value in the YEAR (Q2) cell. I guess it's better to pickup the blank rows, than to skip all the rows.
 
You must log in or register to reply here.
Back
Top