Loop Through Rows Until Blank And Call Macro
Feb 16, 2007
I have a spreadsheet with a list of names from cell A5 to A? - can be as many as 130 names or as few as 20. I have to create individual spreadsheets with the persons name as part of the filename. I have 3 subroutines which work manually ( CreateFile, LookUp and NewWorkbookSave ) so that I can generate individual files for those users who require them. However I need to be able on occasions to create a full teams worth of files with one click - to this end I need to be able to loop through cells A5 - A whatever and call the Subroutine CreateFile - I enclose the code I have so far
Const Fname = "AON Ver 3,0.xls" 'insert filename here like "AON Ver 3,0.xls"
Const DirName = "\Ukplscs105central repositoryAOMTally SheetsTeam upload Tally sheets"
Const TeamName = "AON" ' insert team Name here like "AON"
Const FilePath = DirName & TeamName
Dim count As Integer
Sub CreateFile()
ChDir FilePath
Workbooks.Open Filename:=FilePath & Fname
Range("C3").Select
ActiveWindow.WindowState = xlMinimized
Call LookUp....................
View 5 Replies
ADVERTISEMENT
May 14, 2014
Macro which loops through a number of files and calls the same macro in each of them. Unfortunately when I add "Application.Run..." to the code, it no longer loops through the process and instead stops after updating the first file in the loop. If I remove the "Application.Run..." code and add any other code, the loop works fine and it continues through the process repeating all the steps for each file found.
Why it stops after one file when using "Application.Run..." to call the macros?
NB I have a list of path and file names starting in row 8 of columns A and C. Each file in the list has a macro called UpdateS1 and promoupdate1.
Sub C_Run_Loop_Macro()
Dim lastRow As Long
Dim i As Long
[Code]....
View 4 Replies
View Related
Apr 22, 2012
Using excel's text to speech I've put together a basic spreadsheet.
[URL]
Code:
Function talkit(Speech)
Application.Speech.speak (Speech)
talkit = Speech
[Code]....
View 9 Replies
View Related
Jun 3, 2014
create a macro that will insert a certain number of blank rows to my spread sheet. The number of rows is determined by the #of tasks calculated for that day and is in column B. For example if in column A the date is 01-07-14 and in column B it has 7, I would like 7 rows inserted with the date in column A to remain the same in all 7 rows. Ideally I would like to use this macro for every row, so if the next row down has 02-07-14 and 6, I would like it to add 6 new rows with the same date.
I have attached a copy of the spread sheet for reference.
View 4 Replies
View Related
Oct 16, 2007
If ComboBox1.Text = " Market 1" Then
StoreNumList = Array ("66", "67", "107", "222") 'obviously array string is much longer than this
For x = LBound(StoreNumList) To UBound (StoreNumList)
StoreNum = StoreNumList(x)
ComboBox1.value = StoreNum
Call btnOK_Click_Process
Next x
Program runs fine first step through. It calls the btnOK_Click_Process sub routine just fine. However on second and subsequent For/Next cycles it does not call btnOK_Click_Process.
View 9 Replies
View Related
May 12, 2009
I am having a problem calling buttons. I have an array with button names and another array with their caption texts and I would like to call them one by one in a for loop:
For i = 1 To UBound(button_array)
Sheets("Statistic").Shades(button_array(i)).Caption = button_text(i)
Next i
But the only way I seem to be able to call them is by their set names, like:
Sheets("Statistic").statistic.Caption = button_text_ENG(i)
View 2 Replies
View Related
Feb 9, 2013
I would like to have my macro code search column A (supplier numbers) and split the rows into groups of rows of 5 or less and then insert 3 blank rows between each group of rows. The split needs to start on a new supplier number and cannot split a supplier number into two different groups. Here is a sample:
Supplier
Invoice Date
GL Date
Invoice Amt
[Code].....
View 1 Replies
View Related
Feb 15, 2014
I have been using this macro to separate lots of rows with a certain amount of blank rows.
Example:
Code:
Sub test()
Dim j As Long, r As Range
j = InputBox("type the number of rows to be insered")
Set r = Range("A2")
[Code] .......
I trying to change it so that, instead of inserting how ever many blank rows, it just copys the row above.
Example (Row A to C)
I have been messing around with macro recorder and i could select each row and paste it into the blank rows, but from researching on the internet selecting and pasting data seems to be a waste of resources (ram?) and i will be running this on a couple hundred rows (lots of data). Also, i would like to keep the functionality so that i can still choose how many times it copys/inserts each individual row from a input box.
I eventually want the data to end up like it is shown in the table but i am takin it one step at time because i want to understand what the code is doing.
View 1 Replies
View Related
Dec 28, 2011
I have this in Column A, with about 120 entries:
Company 1
Contact 1
Address 1
City, ST, ZIP 1
Phone 1
Fax 1
Company 2
Contact 2
Address 2
City, ST, ZIP 2
Phone 2
Fax 2
Company 3
Contact 3
Address 3
City, ST, ZIP 3
Phone 3
Fax 3
I want this:
Company 1 Address 1 City, ST, ZIP 1 Phone 1 Fax 1
Company 2 Address 2 City, ST, ZIP 2 Phone 2 Fax 2
Company 3 Address 3 City, ST, ZIP 3 Phone 3 Fax 3
all the way down.
I can't figure out how to record the macro to tell it to then skip the blank line, collect the next set of data, and put it in the next row. I can do it for two, but then it just replaces the first two with the next two and I lose data.
View 5 Replies
View Related
Nov 9, 2006
I've taken over a spreadsheet that has a macro in it. However it doesn't seen to function properly. The big issue with the Macro is that it is only running for the first 80 lines of data. I can't have a restricted range as the number of lines could increase/decrease each month depending on activity. I've attached the code.
Sub Macro1()
Dim strTemp As String
Dim intSpace As Integer
Dim intZero As Integer
Dim i As Integer
Dim strCat As String
Dim strZero As String
Dim intLoopCounter As Integer
Dim rw, this
intLoopCounter = 1
For Each rw In Worksheets(1).Cells(1, 1). CurrentRegion.Rows
this = rw.Cells(1, 1).Value ...................
View 8 Replies
View Related
May 20, 2009
I need to be able to run a macro that will find the word “report“ within a cell in column A. Once found it I then need it to delete that row, the row above and then the 9 rows underneath it. Once it has deleted that data I need it to then move to the next and so on.
It’s a report I have dropped into excel which repeats the page header. E.g the following is repeated constantly which I need to be deleted:
¬¬¬¬¬¬¬¬¬¬¬¬¬
Report: xxx
Company: xxx
Turnover By C
01/04/2008 To
**THIS REPORT
Customer: 0 T
¬¬¬¬¬¬¬¬¬¬¬¬¬
Account
Cust Num
¬¬¬¬¬¬¬¬¬¬¬¬¬
View 14 Replies
View Related
Apr 17, 2014
I am trying to get a macro to run in excel that takes a simple text to columns command in one line of data and runs the command on a loop through however many rows of data there happen to be.
I've attached two screenshots - one with what I've got now (Before.jpg) and what I'd like to have after the macro runs (After.jpg). The code below is what I used to get the first text to column breakout, which I can hopefully run on a loop to breakout anything in the DEPT column that contains a "/". It can ignore the rows that only have one department to begin with.
Selection.TextToColumns Destination:=Range("K2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Eventually I'll want to create another macro that transposes the breakout text back into the DEPT column and populates it with the corresponding data, but I figure I'll take things one step at a time.
Attached Images
Before.jpg‎
ter.jpg‎
View 10 Replies
View Related
Dec 15, 2008
I need to query a master spreadsheet and pull out all the rows on it that have the same name as I have in cell A1 of my spreadsheet
e.g.
Myworkbook, sheetname = queries
Cell A1 = "ABC"
The Macro queries the "test Data" workbook (c:myfolder estdata.xls) "sheet x" and looks in column A for ABC, if it finds ABC it will copy the row and paste it into Myworkbook, sheetname = queries. There are often several rows of data that need to be copied that all have "ABC" in column A.
Also, in column B of the test data workbook, is a number, the highest value denotes the "version" of the data, the highest number is always the latest version of data that should be retrieved, e.g. if 9 the highest value in column B then all data that has ABC in column A and has 9 in column B should be retrieved, all other records should be ignored.
I guess this is a sort of a macro loop but not too sure how to do it.
View 10 Replies
View Related
Nov 13, 2012
I would like VBA code to delete all rows where there are blanks in Col B
View 2 Replies
View Related
Dec 13, 2007
I need to write a macro that will successfully delete blank rows. I also need to write a macro that will successfully delete duplicates. The duplicates are numbers and they do constantly change.
View 9 Replies
View Related
Mar 3, 2008
I am able create a macro using the find function to find the next blank row, but I would like to have it select a range of rows down that I can then delete. Each time I run the macro the next blank row may be different thant the last, so it can't be a set number of row numbers each time. I could also possibly use the print area function if it would be easier.
Here is what I have so far, what is in red is where I need it to vary from next active blank row down to R2001C14, and then delete all the active blank rows ...
View 9 Replies
View Related
Jun 12, 2008
I need a macro to delete blank rows from row 2, but excluding the last 4 rows where there is blank rows between the data
See example below where the rows containing blank cells after #2 in column A must not be deleted ...
View 9 Replies
View Related
Apr 3, 2014
I am looking for some VBA programming that will allow me to use 1 button to consolidate rows (delete them). The problem I am running into is that the rows I want to delete have formulas in them so I can use "find all blank cells & delete" type of macro.
Capture.PNGCapture1.PNG
Attached are a couple of brief images on a small scale what I am looking to do.
I want a user to be able to have this sporadic list of choices, consolidate them so the are all one right after each other, then export to a word file for editing later on.
View 1 Replies
View Related
Dec 11, 2008
I need help writing a macro. I only have experience recording them, not writing them in VBA.
I need to check columns A and B for blanks. If A and B are both blank, I want the macro to delete the row and move on to check the next row. I need to perform this macro for the entire worksheet.
View 8 Replies
View Related
Mar 8, 2009
I've added a button with a macro to delete any blank rows in a worksheet.
Cell A3 is completely blank and A6 has an IF formula which returns "". In my macro it copies the whole worksheet and then paste special values, to take away the formula in A6 and then deletes the blank rows but it still does not delete row 6.
View 4 Replies
View Related
Jul 30, 2009
I'm trying to find a way to delete blank rows that contains formula. I have two excel sheets.
sheet1 contains information and sheet2 references the values from sheet1. Let's say I put the following values in sheet1:
ColA
AAA
(blank)
(blank)
BBB
(blank)
CCC
After entering above values in sheet1, here's what sheet2 would look like:
ColA
AAA
(blank that holds formula) =IF(ISBLANK(Sheet1!A2)," ",Sheet1!A2)
(blank that holds formula) =IF(ISBLANK(Sheet1!A3)," ",Sheet1!A3)
BBB
(blank that holds formula) =IF(ISBLANK(Sheet1!A5)," ",Sheet1!A5)
CCC
What I really wanted to happen is for macro to eliminate blank rows in sheet2 to look something like:
ColA
AAA
BBB
CCC
Is it something possible?
View 14 Replies
View Related
Sep 14, 2012
I recently found this macro on these forums for deleting rows where all of the cells are empty.
Code:
Sub RemoveEmptyRows()
On Error Resume Next
With Range("B300:B1000")
[Code]....
View 9 Replies
View Related
Sep 25, 2013
I would like code that will delete all rows where there are blank cells in Col D from row2 onwards.
View 2 Replies
View Related
Apr 28, 2014
What I have are dates in Column B with a lot of blank rows in between. For example: cell B2 has a date in it and then the next date would be on cell B54. I need a macro to copy cell B2 until it finds a new date (which is in cell B54) then copy the date in cell B54 until the next date again.. so on..
View 9 Replies
View Related
Sep 11, 2013
I have working code that returns a row number within a for loop based on parameters I set.
Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.
Code:
for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row)
if x = y then
*storedRow = rowNum
end if
next rowNum
*
Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.
All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.
View 4 Replies
View Related
Mar 28, 2014
I have a userform with 16 Textboxes Named "Text1" through "Text16". What I am trying to do is apply code that if there is content in the textbox it generates a new row after row15 and inputs the text in cellA of that row. Ex. "Text1" = JOE "Text2" = TONY......Hit Commandbutton1 on userform. It inserts Row16 puts "JOE" in A16.....then sequentially it inserts Row17 puts "TONY" in A17.
View 14 Replies
View Related
Jan 10, 2012
Can a macro make a workbook everytime you copy, it will paste special formulas only and skip blank rows? And can I still let me select the range manually? I would like to use this to link workbooks.
View 2 Replies
View Related
Jan 30, 2013
I have spreadsheet of data, I need to extract any rows that have blanks cells in columns F or P or T.
If possible I would like a macro I could run that would cut all of the rows that meet the above criteria and paste them in to a separate sheet.
View 2 Replies
View Related
Mar 11, 2014
I have a macro which opens a csv file, moves a few columns about then pastes the data into a master workbook. The problem I have is that even though there is only around 1,500 rows of data on the csv file, the sheet on the master workbook is showing up to row 1,048,576 after the data has been pasted in! This is causing issues as I use this data later on in the macro and there are a couple of 'find and replace' commands that take an age to run because of the number of blank rows.
Code:
Workbooks.Open Filename:= _
"L:PVDDBB15." & Format$(Sheet1.Range("G5"), "ddmmyy") & ".csv"
Columns("AB:AB").Select
[Code].....
View 7 Replies
View Related
Oct 1, 2008
i have created a spreadsheet to simplify our work flow, I am stuck on what is probably the easiest of the commands.
basically have rows dedicated to specific codes and the colums represent values relating to each code, all codes have a different set of values, the attached example only has a few variables but the actual worksheet will have several hundred.
the idea is the user will input the code they wish to get details on in A2 and then press the command button and it will then show (as per the after sheet in the attachment) just the relevant information for that code, so filter the code in column A and hide the columns which hold no value.
where i am getting stuck is I am not sure the best way to proceed, is it best to create the macro button to do the filter and hide or is there a better way using vlookup and a pop up window asking for the relevantcode to be inputted to to retrive the information, again understand there will be hundreds of colums and hundreds of rows and the values may be 20 or 30 colums apart for some of the Codes so this simplification is really saving the user a lot of time.
View 7 Replies
View Related
