Solved

Can this VBA code be made more efficient

Posted on 2015-01-30
16
61 Views
Last Modified: 2016-02-10
Attached is a workbook, with a Test worksheet.  This is just a small portion or the 500+ Rows that it has to format and copy.  The embedded code takes longer than what it would seem for only 500 rows.  Can only feel that the code is not as efficient as it could be for something really simple.

The code works as intended...crearting a sheet of "Labels", so the formatting has to be pretty exact.  The reason for the copy and paste is that in the original, there is a formula in each of the cells that point back to the data that is listed on the sheet. So, the copy and paste is also critical, or else done by hand.

Just wanted an Expert to take a look to see if there are ways to improve these few lines of code.
Label-Test.xlsm
0
Comment
Question by:Cook09
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
16 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 40581265
Well the workbook that you posted at first glace has an error !!! at line
lLastRow = wks.Range("B" & wks.Rowks.Count).End(xlUp).Row

Then an other at line
Set wkb = Workbooks("Label Test.xlsm")

it is not a big deal to fix it but this brings me to a question:

You mention in your post:

The code works as intended...

Are you positive you posted the correct workbook ? or ???
... Just before going in the optimization of the code.

gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40581271
More over

I don't understand what this code is doing or I should say what is it supposed to do !!! ?

Can you please advise what is the purpose of this code why you have this code ? What is it supposed to do ?
gowflow
0
 

Author Comment

by:Cook09
ID: 40584022
Goflow - yes your assumption would be correct if those errors were in the original.  Prior to posting I did do a replace all, ws. for wks., and did see the error in the original.  Something must have come up during the writing and posting of the test workbook, where the wks.Rowks.  did not get addressed. But it does work.

The code very simply sets the row height and formatting for the values and/or text that is seen for each label.  Every three lines represent "one label."  The copy and paste is nothing more than copying  the three labels above and pasting in the three rows that were just formatted.  Since the columns stay the same, then from a relative formatting perspective, the rows change to reflect the values on the reference page.

Hope this helps with understanding what the code is doing.

Cook09
0
Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

 
LVL 31

Expert Comment

by:gowflow
ID: 40584244
Well let me tell you what the code do as I am sure it is not correct.

The code clear Col E and F then it goes thru all the rows and sets their height and then copy col A to C for every 3 rows to A and C !!! Yes it copies the label from where it is to the same place.

Let me tell you what is my assumption that the code should do but first have question

1) Will the labels be in Col D,E,F ?? Like you print them from D, E, F or from A, B C
If from D, E, F then for sure the code is not doing that.

2) All labels are originally in Col A,B and C so the question is are they already formatted ? like their row height is correct ? or they come all same row ???

To understand I need your raw data not the way you have worked it as for sure the code means nothing if you already manipulated the data.

I need to have the data as-is and the code that modify this data as-is then I can look it up to see if it can be optimized.

If you want help on this you have to make an effort to answer asap or else I may forget about it.

gowflow
0
 

Author Comment

by:Cook09
ID: 40584696
Attached is a file that should be more to your liking. In checking the code:
Set wkb = Workbooks("Label Test.xlsm") 

Open in new window

this does not appear to be an error. Stepped through using F8 and no error was flagged.

The original 12 as expanded and ran using 25 rows as a stop and the worksheet displays as such.

Yes, you are right, it copies the rows above and places it in the rows that were just formatted. The columns stay the same, but the rows adjust value. The question is, is this the most efficient?
Label-Test.xlsm
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40585768
What is the sheet Handheld Info for ? is it the main sheet from which you produce the Test sheet ? or you have the Test sheet always with formulas ??

In Col D1 you have Label1, D4 Label2, D7 Label3 what is it ?? You want all the labels from Col A, B C to go in sequentially in Col D ???

I still do not understand what you want ? you said revise the code.

My question:
Are the rows always like we see them ?? like with the correct formatting and row height ? its not clear to me why the macro keep on looping to adjust the rows it is as if the data is in flat rows and then need to be adjusted. I need to see how the data is and how you want it to be transformed.

gowflow
0
 

Author Comment

by:Cook09
ID: 40589073
Everything is there in the Workbook just provided. Did you by chance check the formulas in the Test worksheet? The Worksheet "Handheld Info" is used within the formula on the Test worksheet.  Do not worry about anything past column C.  This program works. I have done all 150+ labels and have already printed them, which are now in front of me...but may need to add or change a couple of items and rerun it.

My suggestion for you to see this, as you doubt that it works as described, is to delete rows from 4 to the end and set all those rows past 3 to 12pt or something, then run the macro.

The only analysis required is, is there a better/faster way to do this, given that there are over 150 devices that requires three rows each, and seems to take longer than what it should.  If this is as efficient as it could be, then I'll just use it as is...
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40589084
Yes correct and I hope you can answer fast so we finish with this.

1) Can you pls for my testing upload the 500+ labels you have asis !!!!
2) Is the sheet Test Always like this and you put the new data in  "Handheld Info" ? like every time you have new data it goes in  "Handheld Info" and then you run the macro and print the labels ???

gowflow
0
 

Author Comment

by:Cook09
ID: 40590021
Everytime data goes in, it transfer to the Barcode page, that's why there are blanks.
Barcodes-Labels.xlsm
0
 

Author Comment

by:Cook09
ID: 40590026
They are not always printed right away, but that's what Cols D-F are for, one offs.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40590592
ok it is more clear now !!!

Question:
Is it an option to Sort data in sheet Handled Info ?

As I see you always get new data in Handheld info and the other sheet this time is "Centers SN FL" last time was "Test" is already formatted with the rows height and the font and has formulas is that correct ???

gowflow
0
 

Author Comment

by:Cook09
ID: 40593791
Sorting is not an option. It needs to reflect what the keeper of the spreadsheet has entered.
Yes, the rows and columns are already set and formatted. The Formulas are also in place, as the program looped through to set these up. The Test worksheet was for an Expert to either run or test a potentially more efficient looping method.

As these may need to be run again at some point, I just want to verify that the  current code does this as efficiently as possible. I could leave it as is, but want to self-check with someone, to see if a better method exists to produce these labels.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40594347
The code is not at all efficient is you want my advice. But prior to modifying it I was asking all these question to exactly know the impact of changes.
Now it is clear we cannot sort which is not a problem. I will just advise something final if I can give you all the labels that are relieved from the blank labels in Col D to F would it be fine for you. You would still have the original in A to C and D to F are the ones you print is this fine ?

gowflow
0
 

Author Comment

by:Cook09
ID: 40598686
Yes that would be fine...I can then study how you did it and incorporate the process, for anything in the future that may require similar code.

cook09
0
 
LVL 31

Accepted Solution

by:
gowflow earned 500 total points
ID: 40601073
ok here is my solution:

It uses a different approach it assume that your row high is already set and it copies the format of the entire columns A:C to D:F
Then it start looping thru all the labels and fill an array with only the real labels and all the empty ones are ignored.
Once all the filling is complete then in 1 instruction it dumps all the array in D:F.

Here is the code and my Sub is called PrintLabels I kept your former Sub as well. Just run mine and you will get the results and see in how long it executes.

Sub PrintLabels()

Dim I As Long
Dim MaxRowH As Long, MaxRow As Long
Dim WS As Worksheet
Dim WSH As Worksheet
Dim Row As Long, Col As Long

'---> Disable Events
With Application
     .ScreenUpdating = False
     .EnableEvents = False
     .DisplayAlerts = False
End With

'---> Set Variables
Set WS = Sheets("Centers SN FL")
MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row

Set WSH = Sheets("Handheld Info")
MaxRowH = WSH.Range("A" & WSH.Rows.Count).End(xlUp).Row
ReDim vLabels(MaxRowH, 3) As String

'---> Sort Handheld Info sheet on Col A
'WSH.Range("A1:D" & MaxRowH).Sort Key1:=WSH.Range("A1"), order1:=xlAscending, Header:=xlYes, MatchCase:=False

'---> Make Sure formating in Col A:C will be duplicated in Col D:F
WS.Range("D:F").Clear

'---> Copy the data from Col A:C to D:F
WS.Range("A:C").EntireColumn.Select
WS.Range("A:C").EntireColumn.Copy
WS.Range("D:F").EntireColumn.Select
WS.Range("D:F").EntireColumn.PasteSpecial xlPasteFormats
'WS.Range("D:F").EntireColumn.PasteSpecial xlPasteValues

Row = 0
Col = 0

For I = 1 To MaxRow Step 3
    
    For J = 1 To 3
        If Not IsError(WS.Cells(I, J)) And Not IsError(WS.Cells(I + 1, J)) And Not IsError(WS.Cells(I + 2, J)) Then
            If WS.Cells(I, J) <> " " & Chr(10) & "SN:   FL: " And WS.Cells(I + 1, J) <> "* *" And WS.Cells(I + 2, J) <> " " Then
                vLabels(Row, Col) = WS.Cells(I, J)
                vLabels(Row + 1, Col) = WS.Cells(I + 1, J)
                vLabels(Row + 2, Col) = WS.Cells(I + 2, J)
                Col = Col + 1
                If Col > 2 Then
                    Col = 0
                    Row = Row + 3
                End If
            End If
        End If
    Next J
Next I

WS.Range("D1:F" & MaxRowH) = vLabels

'---> Enable Events
With Application
    .EnableEvents = True
    .DisplayAlerts = True
    .ScreenUpdating = True
End With

'---> Advise User
MsgBox "Labels ready for printing in D, E, F", vbExclamation

End Sub

Open in new window


Pls let me know
gowflow
Barcodes-Labels-V02.xlsm
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40619060
Any chance for you to have tried it out ?
gowflow
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question