Cleaning up Macros and stringing them together?

JM144 · Jul 11, 2011

I have four macros that I have Written/Copied/Recorded. If I use all four of them in specific ways my task is completed to my specifications. I want to be able to string all four of these together into one seamless macro, however, there are some caveats. The First and third macros only need to be run once, while the second and fourth need to be run a variable number of times. The second macro must be run until it reaches a certain name (the "182" is an index number that is common to the beginning of each evaluation of data, I need to stop at a particular name, not a number) and the fourth runs until it can no longer find any rows with the text "page" in it. Also, the macros are undoubtedly written in an extremely inefficient way. Any help streamlining any or all of these pieces would be greatly appreciated. I have posted the code below.Thanks in advance!

 Sub Reposition()
Range("A1:F10000").Select
Selection.Cut
ActiveWindow.ScrollRow = 3889
ActiveWindow.SmallScroll Down:=507
Application.Goto Reference:="R10001C1"
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=18
Range("A1").Select
End Sub

Sub Cleanup()
ActiveCell.Offset(2, 0).Range("A1").Select
Cells.Find(What:="182", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate
ActiveCell.Range("A1:B1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Application.Goto Reference:="R3000C2"
Selection.End(xlUp).Select
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Select
Selection.Copy
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.End(xlUp).Select
ActiveCell.Offset(0, -1).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.EntireRow.Delete
Selection.End(xlDown).Select
End Sub

Sub DeleteBlankRows()
Dim i As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
Range("A1:C5000").Select
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

Sub DeletePageData()
Range("A1").Select
Cells.Find(What:="page", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
Selection.EntireRow.Delete
End Sub

Michael M · Jul 11, 2011

Hi and welcome to the Board.
A couple of questions
1. why the reference to exactly F10000 and then paste it into row 1000 ?
Would you not be better off finding the exact last row of your range and
referencing that?
2. What is Sub Cleanup actually doing, it seems to be all over the place ?
3. I'd suggest you also need to go through and remove ALL the Select.Selection parts, see the cleaned up Sub below
4. Please use code tags to enclose your code, really makes it easier to read. So, [ code ] at the beginning and [ /code ] at the end

Code:

Sub Reposition()
Range("A1:F10000").Cut Destination:=Range("A1000")
Range("A1").Select
End Sub

JM144 · Jul 11, 2011

You're absolutely right regarding the F10000 reference. All I need to do is find the last row in column "C". I guess this would help to speed things up? Sub Cleanup is finding a cell with a name and account # (beginning with "182") in the top left corner of a group of data that needs to be extracted. The entire set of data is pasted directly below a finished set of data. The name and account # cell is then copied and pasted in column A, in front of all rows of data that correspond to it. The index row is then deleted and the end of that data set is found in order to repeat the process for the next set of data. Sometimes the current range has a "page "" " at the bottom of the contiguous data that is why I have the fourth macro. It a a lot of steps and I don't have much/any experience. It is some ugly code and I appreciate your help. I have always tried to learn things by jumping in the deep end!

Are all the Select.Selection parts useless? Curious as to why that is recorded in the first place.

Thanks so much for the advice!

Michael M · Jul 11, 2011

Sorry, I'm flying in and out at the moment with meetings.

Are all the Select.Selection parts useless?

Yes they are...As an example record a macro for changing page setup from Portrait to Landscape, and you'll see heaps of useless recorded info. Recorded macros will do everything you need (almost), but also come with a lot of baggage.
As for finding the last row, this is how it's done in the first macro.

Code:

Sub Reposition()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "C").End(xlUp).Row
Range("A1:F" & lr).Cut Destination:=Range("A" & lr + 1)'will paste one row paste the last row
Range("A1").Select
End Sub

I don't have the time right now to play with the 2nd macro, I'm back in a meeting.
This will bump you back to the top of the list and if there are no responses, I'll have a look tonight..sorry.

JM144 · Jul 12, 2011

No need for apologies! I really appreciate the help. You're a lifesaver! I have a massive reference book for VBA but there's something about having someone show you how to do something. After examining your code it makes perfect sense to me (not to mention making things very clean). I wonder, does this format lead to more efficient processing of code in excel?

I have been struggling with this series of macros for some time. Just to reiterate, do you think it will be possible to link all four of these together while looping both two and four? Thanks again, I look forward to hearing from you.

Cindy Ellis · Jul 12, 2011

If you can describe the logic that you use "interactively" to decide when to run (or stop running) macros 2 and 4, you can code that into a loop (there are several options for loops in Excel, depending on the specific needs).
It's a bit challenging to work on a macro without appropriate sample data, but if you can put into words how you know when to run numbers 2 and 4, I'll try.
Cindy

JM144 · Jul 12, 2011

Thanks for the reply Cindy. The logic behind the number of times each macro must be run is as follows

1st Loop (second macro): the criteria cell that I activate with the "182" also
contains a name. This name (starts with a "Z" is last in the series of relevant data. I could just
let the loop continue running but there are
more irrelevant "182" cells. Continuation of this loop
would compromise the integrity of the reformatted
data.
2nd Loop (fourth macro): This loop can continue until there are no more rows
with the word "page" in them.

I apologize for not having sample data. I know it makes things more difficult to conceptualize. I do appreciate your efforts though!

Cindy Ellis · Jul 12, 2011

I think I need to ask a more specific question.
Relative to the "currently selected" instance of a cell that contains 182, which cell contains the magic name that tells you you're finished? There could be more than one name that starts with Z...is it always the same, or are you possibly comparing it to the next instance of a name next to a "182" to see if the "Z" name you're looking at is the last one?
Are the cells containing 182 always in the same column (e.g. Column A)?
Thanks,
Cindy

JM144 · Jul 12, 2011

The cell that is being looked up with the find function with "182" also contains the name that I would be looking for the format would be similar to this

Zimmerman, Alex 18200451 Rep. ???????? (182 is a prefix for all account #'s)

This data is contained in one cell in column "C" for all individual accounts that need to be looped through. If Zimmerman were the last name in the relevant range, could you assign it a variable and use that to close the loop. This name would need to be changed if another name came after it alphabetically but I cannot think of any other way to close the loop. I'm new and have no exposure to loops yet. Thanks so much!

Michael M · Jul 12, 2011

Just back in for a mo' again.
Thanks for jumping in Cindy.
To get rid of the "page" rows...assuming the word "page" appears in column "A", you could use this inside the first macro

Code:

Sub Reposition()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "C").End(xlUp).Row
Range("A1:F" & lr).Cut Destination:=Range("A" & lr + 1)'will paste one row paste the last row
for r = lr to 1 step -1
  If range("A" & r).value="Page" then rows(r).delete
next r
End Sub

Cleaning up Macros and stringing them together?

JM144

New Member

Excel Facts

Michael M

Well-known Member

JM144

New Member

Michael M

Well-known Member

JM144

New Member

Cindy Ellis

MrExcel MVP

JM144

New Member

Cindy Ellis

MrExcel MVP

JM144

New Member

Michael M

Well-known Member

Similar threads

Forum statistics

Share this page

Cleaning up Macros and stringing them together?

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock