I can only do some basic macro work, so now I'm stuck. I am attempting to copy a formula all the way down the sheet to the last row of data. Some days the data could go down 600 rows and another day only 220. How do I make the macro stop so I am not ending with removing bad info?
Last edited by excel1jennifer0; 05-05-2023 at 09:16 AM.
You can make the range dynamic by using something like this:
It will find the last populated row in column A and copy the selected cell content only down to that row. It could be based off a different column but I've used A in this example for simplicity.
Sub DynamicFormulaRange() Dim lr As Long lr = Cells(Rows.Count, "A").End(xlUp).Row Selection.AutoFill Destination:=Range("D2:D" & lr) End Sub
There are also other ways of accomplishing the same and which you use will be down to personal preference and/or the process in hand.
Is the Excel file you attached what your data looks like before or after running your code? Reason I ask is it's obviously a recorded macro and therefore terrible as far as VBA goes. I was going to rewrite it for you to make it a bit easier to manage in future but if the attached isn't the data pre processing then I'd need to see what that looks like.
BSB
Hello! Thanks for your assistance. I am adding both the raw version and the completed version. I appreciate your help.Attachment 828177Attachment 828178Attachment 828179
Last edited by excel1jennifer0; 05-05-2023 at 09:40 AM.
So if you run your current code on the raw data you end up with the "as completed now" version, and the difference there is a load of blank rows with borders at the end of the data that you no longer want.
Are there any other differences? Easier for you to tell me about them than me trawl through and compare row by row
BSB
Sorry I did have a small informational line that I must have erased. So yes, once I run the macro I either get two errors: it doesn't fill the times in all the way (because the macro only runs to 350, although there may be 500 rows of data) and the borders for each row continue down whether there is data or not all the way to 650. I'd like it to adjust based on the rows with value instead of the basic 350. The only other items it doesn't do now is basic formatting stuff: expand the column to fit the txt, highlight parties of 10 in column F and sum it at the bottom. Those are all items I do after running the macro.
No problem at all. Leave it with me and I shall report back shortly.
All column not hidden need fitting to width?
Parties greater than 10 are highlighted yellow?
BSB
Columns that are hidden can stay hidden, no fit needed. They can be deleted or hidden. Parties of 10 are highlighted in yellow, yes. I really appreciate your help. *runs to buy VBA course*
Haha! I should get myself on a VBA course too! Haven't bothered in nearly 20 years of writing code
This took me a bit longer than planned as I was getting confused. There are hidden columns in your example output files that don't appear in your raw data file so I had to mess around with it a little.
Hopefully it works but happy to amend further if it doesn't.
Give the below a try and see if it does what you need. It certainly seems to work on the sample raw data you attached above.
If it does do what you need then I can add comments to it to explain what each line is doing, if that would be helpful. Probably a better learning tool with a real world example than any course you could go on...Sub Arrival2() Dim lr As Long Dim i As Long With ThisWorkbook.Sheets("Sheet1") lr = .Cells(Rows.Count, "C").End(xlUp).Row Columns("A:B").Delete Shift:=xlToLeft .Range("D1").ClearContents .Range("D2:D" & lr).Value = "12:00:00 AM" .Range("C1") = "Est. Arrival" .Columns("E:E").Cut: .Columns("L:L").Insert Shift:=xlToRight .Range("C2:C" & lr).FormulaR1C1 = "=RC[1]+RC[8]" .Columns("L:AA").EntireColumn.Delete .Columns("D:D").EntireColumn.Hidden = True With .Range("A1:K" & lr) .Borders.LineStyle = xlContinuous .Font.Size = 14 .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .RowHeight = 34.5 End With .Range("A:A,C:C,G:K").EntireColumn.AutoFit .Columns("B:B").ColumnWidth = 16.8 Rows("1:1").RowHeight = 43.2 With .Range("A1:K1") With .Interior .ThemeColor = xlThemeColorLight2 .TintAndShade = 0.399975585192419 End With .WrapText = True .Font.Size = 18 .Font.Bold = True End With .Columns("H:H").ColumnWidth = 8.38 .Columns("I:I").ColumnWidth = 10.4 .Columns("J:J").ColumnWidth = 10.7 .Columns("E:E").TextToColumns .Range("A1").Select For i = 2 To lr With .Cells(i, "E") If .Value >= 10 Then .Interior.Color = vbYellow End With Next i With .Cells(lr + 3, "E") .Value = Application.Sum(ThisWorkbook.Sheets("Sheet1").Range("E2:E" & lr)) .Interior.Color = vbYellow .RowHeight = 34.5 .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter End With End With End Sub
BSB
Last edited by BadlySpelledBuoy; 05-05-2023 at 09:10 AM.
I received an error when running the macro. I am including the results once the macro ran and a screenshot of where the error occurred.
Macro Error.pngExcel Results .xlsx
Very bizarre! It works fine for me.
All that line is doing is converting the numbers formatted as text to actual numbers. You can delete that line and see if it works from there, but I think the part where it sums the number of PAX at the end will result in 0.
I can look at other ways of doing that if the rest works.
BSB
Last edited by BadlySpelledBuoy; 05-05-2023 at 10:09 AM.
Okay after more diving into this mess, I'm finding there's another macro run before mine. From the original data file, this macro is ran and then I am attempting to run mine. Which is why yours isn't working for me as it doesn't know the earlier adjustments?
From raw file (raw file, no changes attachment), macro below is run.
This gives me the revised version (raw file after macro attachment).
Code also attached. Raw File No Changes.xlsxRaw File Changes After Macro 1 (run_custom_macros).xlsxCustom Macro Code.docx
OK, so what's the next step? The code I provided needs amending so it churns out your expected output AFTER these other macros have been run?
BSB
Hello again. I was wondering if you can still help me? I understand it's a time and effort on your side, if you cannot. Can you at least point me in the right direction to sort of learn on my own? Or attempt to learn? :P
Correct, thank you. I need the file to run the "custom macro" file and then end at my desired goal sheet. The only problem with that one is that it won't continue/stop at the row with the last value and the small details (fitting, highlighting over 10). It would be best to have the custom macro and then a separate one for everything else. We run the custom macro on another sheet which doesn't need all of this extra stuff after. I hope that makes sense.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
excel1jennifer0 
Register To Reply
Bookmarks