I would like to combine the following 3 macros into one macro. Try as I might, I can not figure out how to do it?
Sub DeleteEmptyRows()
Dim Counter
Dim i As Integer
Counter = 5000
ActiveCell.Select
For i = 1 To Counter
If ActiveCell = "" Then
Selection.EntireRow.Delete
Counter = Counter - 1
Else
ActiveCell.Offset(1, 0).Select
End If...................................
I got 2 macros that I want to run in one go. So to explain:
Here is the data of Sheet1 before the macro is run:
A
B
C
D
E
[Code]....
Keep in mind, when Macro2 is run it puts the data on Column W then X then Y etc as soon as the Column reaches 20 it moves on to the next one.
So my question is:
Is there anyway to combine both macros to just run it once instead of running it individually?
I have two macros that I need to run as one and I am not sure how to call the second macro from within the first. I'm sure it's easy...If you know how... Thank you in advance.
--------------------------------------
macro 1
Sub PrintUsedRange()
'
Dim ws As Worksheet
'
For Each ws In Worksheets
Application.CutCopyMode = False
ws.PageSetup.PrintArea = ws.UsedRange.Address
With ws.PageSetup
.Zoom = False
.CenterHorizontally = True
.Orientation = xlPortrait
.FitToPagesWide = False
.FitToPagesTall = 1
End With
Next ws
ActiveWorkbook.PrintOut............
I have a userform that has the same type information on 3 different entities (name, address, phone, etc). The same research is required on each one i.e. you need to look up the zip code for each). Therefore I have a bank of buttons on each page of the multipage form. Each button does the same thing depending on which page you are on. The following is my code to copy some information and open another application depending on which page you are on and which type entity.
My question is how can I combine these into one so my module is not 3' long.
This code calculates a value for column K when you punch in a number in column J (in any rows 17 to 116, separately). The first problem is that when you delete a value out of J, the value stays in K for some rows (where it should only contain a value if J does). I'm guessing it has something to do with the triggering event that I put in bold. Secondly, I need to be able to protect row K at all times from deletion or entering another value. It ONLY should calculate a value based on J. So I need to use the module (below the code) and I want to integrate it into the code or call it in a module. It's not working properly because it prevents ANY and ALL changes, even the ones caused by new values entered in J (which should be allowed). It should only prevent deletion or manually entering a value on the worksheet. So the bottom line is that when you enter a value into J, unless column E says "Annuity" it will calculate using the formula given. If you delete J, K should go away as well. And at no time can you delete or manually change K. And when you switch values in E, it must reloop to check if it says "Annuity" or not, and if not, it must clear J (which clears K).
And lastly, this thing is brutally slow, so maybe there's an easier way to write it. Sorry for all the detail, but I think it's an easy fix for someone who knows programming, but i don't know how to put it together! NML Inventory is the Activesheet.
Note: It should also be noted that, when "splitting" the macros below up into two different macros, they work, but then I have to play 2 macros when I only want to play one.
Just when I thought I was getting this macro stuff, I've come back to reality. I tried to combine these 2 macros together (taking out 1 "sub" and 1 "end sub") and when doing so, I got the error message
"duplicate declaration in current scope"
I thought about removing some "Dim" lines, but realized that the Dims say different things. For example in macro 1, it says "Dim Rng As Range" and in macro 2, it says "Dim Cust As Range".
Don't I need both? Do I put in an "exit sub"?
I am using the following code to identify any blank rows in a sheet and delete them:
View 3 Replies View RelatedI have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
View 6 Replies View RelatedI have a user that keeps a maintenance log in an Excel worksheet and sends an updated copy once a week to a board member. Two weeks ago, the board member started complaining that he was prompted to enable/disable macros on opening and became worried when my user stated that no macros were used in the book. He is now concerned that we have sent him a virus.
I know the file is clean because I've scanned it, and when I look at the file in VB, there are no modules or classes present just the Sheets 1-3 and the ThisWorkbook file. None of these objects have any code in them. My user does have some macros in PERSONAL.XLS but they are not used in the workbook in question.
No one else gets the prompt for enabling/disabling macros. Even if I set my security to prompt for any macros, I get no message. I'm convinced that there must be some setting in his Excel that is causing this individual to get this message. Is there anything else other than a macro that would cause this?
What is the commands or script for deleting a macro automatically using
another workbook macro.
Is there a way to hide a macro from the list where you choose which to run, but not in the VBA editor? The userbox I just created calls upon 2 different macros, and has a macro to bring up the userbox. I need a way to hide the macros in Module3 from selection, but keep the macros in Module4 available to choose to run.
View 9 Replies View RelatedI have two macros. Macros 1 opens another excel file, counts the number of rows it has, inserts that number of rows into the master file and then copies the data over. Key code as follows (x3 for 3 worksheets)...
For k = 1 To numrows
Selection.EntireRow.Insert
Next k
Macros 2 'cleans' the appended data by systematically going through each row, checking if certain cells have data, and if not deletes the rows. Key code as follows (x3 for 3 worksheets)...
For l = lastrow To 12 Step -1
If WorksheetFunction.CountA(Range("B" & l & ":C" & l)) = 0 Then
Rows(l & ":" & l).Select
Selection.Delete Shift:=xlUp
edelrows = edelrows + 1
End If
Next l
Here's the weirdness. macros 1 runs fairly quick the first time out (few seconds). Macros 2 takes about 2 minutes to run (which I'm fine with). However, when I run macros 1 AFTER running Macros 2, Macros 1 goes from taking a few seconds to a few minutes.
how to use excel 4 macros. I do not even know where to put them. I have inherited some spreadsheets that have both Xcel 4 Macros and vba macros in them. (Well, I get a warning that I cannot switch them off when I disable macros on opening a workbook). I cannot even find the offending version 4 macros so that I can re-code them in VBA.
I have some code where I use and statements, as follows:
View 4 Replies View RelatedI want to see if a goal has been met and get a "Yes" or "No" so I wrote
=If(A1>=A2),"Yes","No"
But on months not worked where goal and production both equal 0 then I am getting a return of Yes. How can I say is A1 >= to A2, but if both are 0 return N/A?
I am having problems combining AND and OR into a single statement. To start I have a statement =IF(AND(B978="P",R978=0,S978=0),"processed",""). If B=P and R=blank and S=blank then display PROCEED else make blank.
What I want to do is check if S is blank or "N" but when I try to add an OR I get error messages regarding missings )'s =IF(AND(B978="P",R978=0,OR(S978=0,S978="N"),"processed","").
I have two vba the first one records the date & time of a cell change in column a and places this in column b
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a:a")) Is Nothing Then
Target.Offset(0, 1) = Date & " " & Time
End If
End Sub
The second one is a function to get the User logged in to windows.
Function UNameWindows() As String
UNameWindows = Environ("USERNAME")
End Function
What I really could do with is a way of incorporating the user name in the first function so it records date, time and user?
I have two seperate columns, First Name (Column B) and Last Name (Column C). Is there a way a macro can combine the two into one cell Last Name, First Name.
View 3 Replies View RelatedI am trying to write a UDF function where I lookup if a value is on a range on different sheet. I would like to be able to write what I want the result to be in the function. I also hard coded that the function need 3 range and 3 results, but I know it is possible the make the number of component vary depending on what the user writes. I would like my function to do that.
Here is what I have written so far :
VB:
Function find_para2(var As String, rng As Range, result1 As String, rng2 As Range, result2 As String, rng3 As Range, result3 As String)
If WorksheetFunction.IsNA(WorksheetFunction.VLookup(var, rng.Address, 1, False)) = False Then
find_para = find_para & result1 & ", "
[Code] .....
I need to find a formula to show the time allowed for each person, now they recomend combining 'if' with 'lookup'. now I may be having a blonde moment but I have tried to figure it out for a while now and i am stuck.
View 14 Replies View RelatedTrying to combine Column A (First Name) with Column B (Last Name) Probably very simple.!
View 2 Replies View RelatedI have a big amount of data and want to consolidate it.
I have identical text in Columns 1,3 and 4, but the text in Column 2 is different for all.
How do I make 1 row instead of 3 with Column 2 consolidated?
See the attachment : Excel_list.xlsx
I have a problem in excel that is very urgent to resolve. I have a table that contains duplicate records e.g two people living at same address. I want to merge those records whose address field value is same. Find attached an excel sheet that contains the exact data and the exact output that I want. I know it requires VBA coding.
View 4 Replies View RelatedI have two different bits of code as follows:
View 3 Replies View Relatedok - I have numbers that need to be converted to 12-digit numbers with leading zeros if they are less than twelve digits. for example, 1234567 would turn into 000001234567 to have 12 digits. to do this, i use:
=rept(0,12-LEN(A1))&A1
additionally, i need to strip off the last three digits and replace them with three zeros. my example would now become 000001234000. assuming the result of my first formula (above) is in cell B1, i would use:
=INT(B1/1000)&"000"
Is there a way to combine these two functions into one formula to make this conversion process more painless? Or is there another formula/function I can use that I haven't thought of or do not know?
I'm having a mental block here. I need the below statement to have a max value of (B14*B4) in the instance where
(B17-B19)*0.7 exceeds (B14*B4),
IF(B17-B19
Just starting to learn VBA. I have the 2 following codes.Can they be combined into 1 code.I have 2 buttons one say hide rows and the other button says unhide rows....can 1 button be used and when the rows are visible the button says hide rows and when the rows are hidden the button says unhide rows
Sub hiderows()
ThisWorkbook.Sheets("sheet1").Rows("3:12").Hidden = True
End Sub
Sub unhiderows()
ThisWorkbook.Sheets("sheet1").Rows("3:12").Hidden = False
End Sub
I have two subs which I've been trying to combine into one. Essentially all that is changing is the slicer name and range value.
Here's what I have:
Code:
Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)
Dim cache As Excel.SlicerCache
Set cache = ActiveWorkbook.SlicerCaches("Slicer_Project_Type3")
Dim sItem As Excel.SlicerItem
Dim myString As String
For Each sItem In cache.SlicerItems
If sItem.Selected = True Then
myString = myString & "," & sItem.Name
[code].....
But it's erroring out on me.
how to combine these two codes into one?
Code:
Sub DelTemp()
Dim LR As Long, i As Long
Dim pt As PivotTable
[Code].....
Four worksheets (A,B,C,D) all begin with row2 and end with the last non-blank cell.
What VBA code would merge the information from the four worksheets to Worksheet E?