I have a macro that seems to do all it's supposed to do except it leaves a big chunk of half filled rows right in the middle of my data. I can't figure it out. Any help would be appreciated.
It is as follows:
Please Login or Register to view this content.
Last edited by FDibbins; 06-29-2016 at 12:32 AM.
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.
Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
(I will add them for you - this time)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thank you FDibbons. Appreciate your help. And thanks for the link. I will definitely take a closer look at the forum rules.
can only guess without actual file to work with
toggle some break points at where you think might be the problem go thru it line by line to see what doing what its supposed to
http://www.excel-easy.com/vba/examples/debugging.html
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Hi, Humdingaling
Thanks for the link. I will definitely check it out this weekend when I have more time. But I have tried to "step in" and it's almost like every time I go through it something new pops up. And I tried to attach it last night but couldn't - it was late, I was tired and so frustrated - probably something I was doing wrong. I'll try again now to attach it. I don't see where I can do it here in this reply so I'll go back to my original post and see.
Maybe if you have a few minutes you wouldn't mind taking a look? Truth be told, I am really a novice at code and macros so I have just been beating my head...
Thanks again.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
some reason some methods of uploading dont work for everyone
here are some other methods that might work
http://www.excelforum.com/the-water-...his-forum.html
you'll find coding is mostly just knowing the language/syntax
dont beat yourself too much over it while still learning
im still learning and ive been at it over a half a decade
Hi, again
For some reason this file is massive - 17 mg. I can't figure out why. I've taken it down to almost nothing - 2 sheets, 1 with headers and the second with 250 rows and 3 columns.
But I can't post it because it keeps telling me it is too big.
Sigh.
Last edited by BDD2015; 06-30-2016 at 12:24 AM. Reason: add attachment
Ok, Humdingaling
I think I've done it. I am a little concerned however...
The Tracker sheet has formulas that won't work with the autofill because of how I had to crop it.
And the DayRun sheet can have upwards of 50k rows so I don't know with the couple hundred I have left whether it will still leave a big patch of half compete rows.
Hopefully the bare bones file won't make you want to pull your hair out.
And please don't go crazy with it if the chopping affects it too badly.
Unfortunately I have to log off because my alarm is set to go off to get up for work in 4 hrs.
Fortunately this is a long weekend here in Canada though.
Thanks for your replies, help, links and consideration at looking at this.
Brenda
Hi, Humdingaling
I am still here - plugging along. I think I am making progress, though. I am 3/4 of the way through the macro by stepping into it - lol. My mountain right now is:
On Sheet 1, Row 5, Col. H-M the data is never removed because it holds formulas that I need for the autofill.
So when my macro runs and copies the days data from Sheet 2 onto Sheet 1 all is good.
But on Sheet 1, Row 5, Col. N is the the number 1. That is used as my counter. I can't autofill that column because the 1 increases sequentially, which I don't want. And I don't want the 1's to exceed past the rows with the data.
I think my existing code recorded me copying the 1 from N5 and pasting it down but when I run the macro it fills in far, far too many rows with a 1.
Is there a code I can use to say copy the 1 from N5 and paste it as far down as the last row in Col. M?
Once I get that figured out, I have a small chunk of code to step through and then BOOM, I'd be a happy camper.
Hopefully this was clear and doesn't confuse you.
I'd appreciate if you could help me out with a line or two (?) that would help me achieve that.
Thanks, Brenda
sorry i did look at your file but didnt finished but day end
does the code start on dayrun or tracker?
it seems to start on dayrun but i am not sure
the first issue i have is on the file you provided you did not define what lastcell was
so i stole it from the code you provided
another issue i have with this code is you swap from the two worksheets dont you use the same lastcellPlease Login or Register to view this content.
the last cell should be different on the two worksheets?
Hi, Humdingaling
The code starts on DayRun.
So - mental image of what's going on, I guess, might be good.
Every day I run a report that generates what is seen is the DayRun. The daily run of the report varies in size but it could be as long as 75K rows. The intention of the macro is to populate it in the DayRun, use the macro to transfer it into the Tracker sheet and then manipulate the data. I then transfer the final data into another WB called MasterPDSL. The reason I do this is two fold:
1) Because I use the DailyPDSL file, specifically the Tracker sheet, to sort based on Col. O, "Code" do a sum of like codes, deleting many thousands of rows per day from the final data.
2) And because I want to allow others to do the same thing during other shifts but not go near my master data.
The problem stems from the size this sucker is getting. Three shifts a day, 1 month all shifts, 1 month 1 shift and already it is close to 275K rows. I can't do much of anything in the MasterPDSL workbook except dump the info. because it takes so long...
general gist of what im getting
you have a sheet on dayrun
you some filtering/cleaning/sorting then copy over to tracker
so there is some color formatting done for this to do anything?
Please Login or Register to view this content.
then this code
highlight what has been filtered in column E,F,GPlease Login or Register to view this content.
fill blank cells with "NR"
correct so far?
So, the code as it now stands is:
Please Login or Register to view this content.
Yes. If you notice on DayRun there are dark grey rows with a subtotal. I get about 5K of them a day so that part of the code filters them out so that the copy/paste process only grabs the visible cells.
And your second question - Exactly!
try this code
Please Login or Register to view this content.
the important difference in code is this line
reset lastcell to the new one in tracker sheetPlease Login or Register to view this content.
OMG! Humdingaling, you did it! I just ran it on 16K rows and BOOM!! Thank you so much - for your patience, your time and help. I so very much appreciate it. I can't believe this nightmare is over and I can get along with the rest of this project.
glad to reach resolution in the end
cheers
Hum
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks