Solved

Can this VBA code be made more efficient

Posted on 2015-01-30
16
55 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
  • 9
  • 7
16 Comments
 
LVL 29

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 29

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
 
LVL 29

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 29

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 29

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 29

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 29

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 29

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 29

Expert Comment

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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
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…
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now