Store Row Number Within Loop And Delete All Stored Rows After Loop?
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
ADVERTISEMENT
Sep 10, 2013
I have output from mainframe that is copy/paste to an excel workbook. I need to have VBA loop and delete the 15 rows. This is mainframe output so it will always be 15 rows
Count
Campus
Name
Course
Start Date
1
336001
Student1
1
9/2/2003
[Code] ............
View 1 Replies
View Related
Jan 31, 2013
What I am trying to do is during a loop operation, which subtotals variable ranges, I want to store the locations of the cells that it puts the sum function into. I.E. if based on criteria it determines that range E4:E12 is summed into E13, I need to save E13 to use in a formula once I'm out of the loop. I don't know how many instances it will find and there's the possibility in the future that not only will the number of instances increase, but the location could always be different as well. Any way to do this without a million lines of code.
View 7 Replies
View Related
Jun 19, 2008
I would like to use array v to store all the results of the for loop u...How can Ido it?
Dim myRange As Range
Dim AnsRange1 As Integer
Dim AnsRange As Range
Set myRange = Application.InputBox(Prompt:="Select row to insert 10 rows below", Type:=8)
AnsRange1 = myRange.Row
Dim u As Integer
Dim v As Integer
Dim var() As Single
v = 0
For u = 23 To 24022 Step 9
var(v) = u
Next u
If Not (AnsRange1 = v) Then
MsgBox AnsRange1
Else
Range(AnsRange1 & ":" & AnsRange1 + 9).Insert Shift:=xlDown
End If
End If
View 9 Replies
View Related
Jul 17, 2008
I am trying to do is use a loop to store data from a range into a 3 or 4 dimensional Array and then output the data from the Array in another range. so for example the i want loop through the data in this range and store all data that is in account 701 into an array and then output this information in another range (tab). The data would have several different account but I only want to see one at a time.
Account Price Amount 701 150 1,000,000 701 125 250,000,000 701 3.25 6,000,000 702 4.25 25,000,000 702 2.35 3,600,000 702 2.55 10,000,000
View 9 Replies
View Related
Oct 12, 2013
I use this code to delete some rows, how I would modify it to work in another spreadsheet where it would "Loop" through and delete rows that start with "User:Kellcyna" down to where the rolls start with "Numbers", and delete the rolls that start with "Total cost center" down to where the rolls start with "Numbers".
The data can contain up to 50000 rolls at times.
Sub Finally()
Application.ScreenUpdating = True
[a:a].AutoFilter Field:=1, Criteria1:="="
[a2:a65536].SpecialCells(xlVisible).EntireRow.Delete
If [a1] = "" Then [1:1].Delete
ActiveSheet.AutoFilterMode = False
[Code] ........
Here is a sample of the data I need the macro to work on. The rows I need deleted are the rows that are highlighted.
User: Kellcyna STANDARD HOURS BY COST CENTER Date: 09/29/2013 Time: 15:10:04 Page: 10
Comments:
Order Op Emp Post Work ctr Setup Unit Planned Earned Total Actual Actual Actual Total Total Orde C R
# Date SU Unit Plnd Stds Setup Run Tme Brd Tme Prod Run Time E
104527059 0010 00000000 09/25/2013 HSW01 0.000 0.4 HR 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 PP01
104523849 0010 00000000 09/25/2013 HSW01 0.000 0.2 HR 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 PP01
[Code] ...........
View 5 Replies
View Related
May 15, 2009
1. Delete all data after row 51.
2. Find the average cost (column 4) and delete all rows where the cost is below average. Thus only retaining rows with an above average cost.
3. Then I want to conditionally format any number in column 6 which appears more than three times.
4. I want to loop this through all worksheets in my workbook.
View 9 Replies
View Related
Aug 11, 2006
I have two worksehets. The first worksheet contains data on products. The product code is in column A. My second sheet contains a list of product codes in column a. I want to delete all rows in worksheet 1 where the product code doesnt exist in the list in worksheet 2. Can someone provide a outline for a macro to loop over sheet one and look up the value in worksheet 2 and delete the row from worksheet 1?
View 3 Replies
View Related
Nov 15, 2008
I have anywhere from 3000 to 10000 rows to delete in a a number of worksheets, but would like to avoid using a loop as even with onscreen update turned to off like this:
View 3 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
Nov 20, 2012
What I have is 3 columns, all containing numbers
What I want to do is delete a row if the middle column is less than 1.
However my loop seems to skip a row if the column data is like the below
a
b
c
1
1
1
1
0
1
1
0
1
1
1
1
1
0
1
End
Code:
Range("A2").Select
Do
If ActiveCell.Offset(0, 1) < 1 Then
Range(ActiveCell, ActiveCell.Offset(0, 2)).Select
Selection.Delete Shift:=xlUp
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = "End"
View 5 Replies
View Related
Dec 31, 2012
I have some code that is working great except that it throws up an error on the last run through because it doesn't find any more rows to delete. I've tried "On Error GoTo" but it doesn't catch it. It seemed to work on my home computer which is running Excel 2010 but then on Excel 2007 I get the debug screen and I don't want that to show up for other users that I share the code with.
Sub DeleteHeaders()
Dim lastrow As Integer
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
[Code]....
View 3 Replies
View Related
Aug 2, 2007
For Each loop can be instructed to loop starting the bottom of the range. I know that a For To Loop can handle looping from the bottom up,
Sub Filterout()
Dim c As Range
Dim rng As Range
Dim i As Long
Dim lrow As Long
Dim counter As Integer
lrow = Cells(Rows.Count, 3).End(xlUp).Row
Set rng = Range("c2:c36")
For Each c In rng
If Left(c.Value, 1) "~~" Then
c.EntireRow.Delete
End If
Next c
View 9 Replies
View Related
Apr 24, 2009
In the code below I find rows containing "$$ #" at the far left and process it.
For all the remaining rows I want to delete the entire row, my macro only deletes some rows. Obviously I am not grasping something about looping throught the rows to delete unwanted data.
View 2 Replies
View Related
May 5, 2012
I am trying to loop through column A and I want to store in an array where I find "App" within the cell value. I am trying to find "App" but will store the whole cell value in the array. I could not figure out the Find method, so I tried the MID function but am having no luck.
Here is my code:
Code:
Sub Arraytest()
Dim arr As Variant, lastrow As Long, i As Long, f As Long, l As Long
f = 0
lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
ReDim arr(1 To 1, 1 To lastrow)
[Code] .....
View 1 Replies
View Related
Nov 20, 2008
First: I am using following loop code to repeat until the last row with data in column B has been reached. I keep getting a compile error and can't figure out to to fix it. The compile error occurs on the ".cell" and ".range" portion of the code and has indicated an invalid or unqualified reference.
Second: Once I have the loop working, I need to reference different cells each time through the loop. The first time through the loop I need to reference cells B2 and C2....the second time cells B3 and C3.....and then B4 and C4....and so on. I am increasing the value of i each time but when I ran the code, I was not getting any results.
View 4 Replies
View Related
Mar 31, 2008
With Sheets("regrade pharm_standalone")
For Each r In .Range("standaloneTerritory")
If r.Value = "X101" Then
r.EntireRow.Copy
Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
End If
Next r
End With
-------------------
I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).
I have a named range called 'territories' that contains the list of X101 -> X152.
I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!
View 9 Replies
View Related
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
Aug 30, 2006
I am looping through each cell in a range and I would like to loop in reverse order.
Dim CELL As range
Dim TotalRows As Long
TotalRows = Cells(Rows.Count, 1).End(xlUp).Row
For Each CELL In Range("C1", "C" & TotalRows)
CELL.Select
'Code here to delete a row based on criteria
Next
I have tried:
For Each CELL In Range("C" & TotalRows, "C1")
and it does not make a difference. I need to loop in reverse order since what I am doing in the loop is deleting a row. I am looking at a cell and determining its value. If the value is so much, then the row gets deleted. The problem is that the next row "moves up" one row (taking the pervious cell's address) and therefore the For Each Next loop thinks it has already looked at that row.
View 7 Replies
View Related
Feb 7, 2008
I have some numbers in a column that I need to copy 12 times (each one) into another column. The problem is that I got like 200 records that will be converted in 15000 aprox. I've uploaded an example of what I need,
View 3 Replies
View Related
Nov 4, 2013
I have a workbook that contains, say, 50 worksheets: the first two worksheets summarise the data and are static in that they don't move position. However, the next four worksheets contain certain data for any given month. Each time a new month comes along, say, November, I insert four new worksheets after the two static ones as a result October's four worksheets are simply moved down the line in terms of worksheet order.
I need a macro to refer to the first six worksheets only (not the other tabs). I opted for index referencing for each worksheet, ie one - six. Now within these six worksheets in any given month, I need to sort the data by a certain column. The problem: in sheets 1,4,5 and 6 I need to rank by column E, but in sheets 2 and 3 I need to rank by column C. I have stepped through the code, which works for sheets 3-6, but doesn't seem to refer to sheets 1-2.
Sub WorksheetLoop()
'
' Loop through an indexed number of worksheets; _
' & this ensures that the worksheet range is dynamic _
' and is able to adjust when new sheets are added/removed, etc.
'
'Dim ws As Worksheet
Dim i As Long
Dim ws As Worksheet
[code]....
View 2 Replies
View Related
Oct 24, 2009
I've worked on a solution for this thread (http://www.excelforum.com/excel-prog...-automate.html) but have been mentally challenged with how to avoid changing the loop counter in one of the loops I have used to resort an array of file names from the getopenfile dialog.
The aim of the shown code (see post 12 of the above link for attached file) is to check if the file containing the macro is included in the array returned by getopenfile while sorting the array of file names, and if so, moving it to the end of the array for "deletion" by redimming the array to exclude the last item. This problem of the open file being selected in the dialog may never arise, but... as the OP's request in the other thread was to allow two-way comparisons between numerous files, I've considered it likely enough to test for.
Here's the code I have settled for esp between the commented lines of hash symbols, which does change the counter (see the commented exclamation marks), but prevents an infinite loop (on my second try!) by using a second boolean flag of "HasCounterBeenChanged". Is there a better way of doing this? Or, alternatively (not in my thread title), is it possible to prevent the active file being selected through one of the arguments in the getopenfilename method?
View 3 Replies
View Related
Jun 21, 2012
Below is the loop. An error occurs at Rows("y:y").Select
The y value that would be found in cell C500 would always match the row that would be selected and deleted. For example if cell C500 had the value 13, I would want Rows("13:13").select and then deleted.
Sub Macro2()
'
' Macro2 Macro
'
'
For y = 1 To 100
[code]......
View 5 Replies
View Related
Oct 3, 2008
Within my loop I would like to delete an entire row - but as my loop goes down each row one by one as soon as one row is deleted the next row moves up and is then not included in the loop.
Dim Rng As Range, Last As Integer, Dn As Range
Sheets("ttool").Select
Last = Sheets("old ttool").Range("J" & Rows.Count).End(xlUp).Row
Set Rng = Range(Range("J2"), Range("J" & Rows.Count).End(xlUp))
For Each Dn In Rng
If Dn "" Then
Last = Last + 1
Sheets("old ttool").Rows(Last).Value = Dn.EntireRow.Value
Dn.EntireRow.Delete
End If
Next Dn
If I run it 4 times it completes as it should. Or if I change it to entirerow.clearcontents all rows are cleared.
View 9 Replies
View Related
May 12, 2006
How do I make a loop to delete a row if the cell A is empty. Example: If cell A3 was empty I want it to delete the row. If cell A4 had data I would like it to skip that row and go on to cell A5.
View 6 Replies
View Related
Jul 13, 2006
I am using this to delete rows from my excel sheet. I basicaly start my script by placing xxx in cell B2000. I want to optimize my code and maybe some how tell excel do until last cell. I am playing with:
"Do Until lLastRow = Range("B" & Rows.Count).End(xlUp).Row"
But not shure how to procede with this.
Range("B1").Select
Do Until ActiveCell = "xxx"
If ActiveCell = "Ticket" Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
View 2 Replies
View Related
Oct 1, 2012
I have two worksheet. One worksheet (ws1) contains a list of item I want. The other sheet (ws2) contains multiple columns where the header (row 6) is named by item name.
I have the following code which deletes the entire column if the header name is not in the list contained in ws1 :
VB:
Sub delete_col()
Dim wanted As Boolean
Set ws1 = Workbooks("test1").Sheets("aaa")
[Code]....
First of all, this loop does not work properly since deleting the entire column shift them on the left, so when I first analyze column 11, if I delete it and then analyze column 12, the real column 12 now became column 11 and so on...
Secondly, this code is pretty slow. I am pretty sure I don't have to loop through my initial item list everytime I do Instr on a new column.
View 9 Replies
View Related
Oct 22, 2012
I have this setup successfully for the deletion of columns and have modified the below to apply to rows, however, I am not seeing the results I expect (or actually anything).
Code:
Sub Analytic_RemoveNA()
'Remove NA
Dim j As Long
For j = 35 To 4 Step -1 'Rows 35 to 4
If Cells(9, j).Value = "NA" Then Rows(j).Delete
Next j
End Sub
I have a data set that spans from B4:I32. If column I has "NA" in any row within that dataset, I want to delete the row.
View 3 Replies
View Related
Feb 24, 2014
I'm trying to create a function that will search for a certain value and if that true, delete certain cells in the same row. So far, I've come with the following and not sure how to write particular line.
So this function will go through column G and count the rows since it could have 1 or 50,000. So for example if column G2 has a value anything other then "Pending" then I need cell T2:Z2 to be deleted.
I know this line is the source of error Rng("T & Rows.Count:Z & Rows.Count").Select but I don't know how to express it.
Sub Check_Status()
Dim iLastRow As Integer
Dim Rng As Range
iLastRow = Cells(Rows.Count, "G").End(xlUp).Row
For Each Rng In Range("G1:G" & iLastRow)
If Not Rng.Value = "Pending" Then
Rng("T & Rows.Count:Z & Rows.Count").Select
Selection.ClearContents
End If
Next
View 2 Replies
View Related
Jul 8, 2009
I'm trying to loop thru the worksheets(which are imported from a web form in Outlook) and delete row 31 until the text "PARTS" is reached.
I need to do this to format each sheet exactly for export to an Access database. The code below works when I step thru it but when I save and run from a button it gets stuck in an endless loop. Any help would be greatly appreciated.
JB
Sub DeleteRow31()
Dim w As Worksheet
Dim rng As Range
For Each w In Worksheets
w.Select
Do Until Cells(31, "B") = "PARTS:"
Range("B31").Select
Range("B31:K31").UnMerge
Range("B31").Select
If Not Cells(31, "B") = "PARTS:" Then
Selection.EntireRow.Delete
End If
Loop
Next w
End Sub
View 9 Replies
View Related