Loop Through Worksheet Delete Row Until Specific Value
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
ADVERTISEMENT
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
Mar 22, 2011
I need a formula or VBA macro to do the following:
I have 2 worksheets, namely sheet 1 and sheet 2.
Sheet 1 contains data in columns A to H.
Sheet 2 contains data only in column A.
I want Excel to check the data found in column A of Sheet 2 with those in Column A of Sheet 1. Hence, when an exact match is found in Column A of Sheet 1, Excel will delete that entire row.
View 4 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
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
Dec 10, 2007
Currently I am using the Kickbutt VBA Find Function of Aaron, but I would like to have something that works more efficiently. What I currently do is (assuming all possible values for Column J are A - F):
Find_Range("A", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("B", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("C", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("D", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("E", Columns("J"), MatchCase:=True).EntireRow.Delete
although I just want some code that says: delete all rows except those that have "F" as content in Column J. I already tried something like:
Range("1:65536").Select
For Each cl In Range("J:J")
If cl.Text = "A" Or cl.Text = "B" Or cl.Text = "C" Or cl.Text = "D" Or cl.Text = "E" Then
Rows(cl.Row).Delete
End If
Next
but it also takes much to long. The major problem I think, is that the number of records is variable so I search the entire worksheet...
View 5 Replies
View Related
Jun 21, 2006
I need a code that will delete a row if it has a specific text in a specific column.
In this case in column W if there is anything that starts with '>TC' I need that entire row deleted that its in.
View 7 Replies
View Related
Apr 16, 2014
I would like to change the greater than number to the value in cell 'I11' in sheet 'Linear Programming Data' and the less than number to the value in cell 'I12' in sheet 'Linear Programming Data'. The code I am using was done by recording a macro since I don't know how to code in vba.
Code:
Sub Results2()
' Results2 Macro
' copy table filter power by greater than and less than
Sheets("Finalizing Results 2").Select
Cells.Select
[Code] .......
View 2 Replies
View Related
Jan 31, 2014
I need to loop through worksheets in a workbook and copy every first cell value(A1) and then paste into a new worksheet.
I have tried various loops. some have copied first value for the first sheet and then pasted in the new sheet. while others have been not so good.
This is the code I have so far and this does not work at all.
Code:
Sub Check()
Dim ws As Worksheet
Dim lr As Long
Dim treg As Worksheet
[Code]......
View 1 Replies
View Related
Feb 13, 2009
I have an excel sheet with set ranges in it. I would like to refer to these ranges in my code, but i am doing something wrong. Here is a portion of the code. The range name is 'Status'
View 4 Replies
View Related
Aug 21, 2014
How do I build a "For specific worksheets" loop?
I have a macro that works for a single sheet but I want it to loop over several. Currently, my code looks like this:
[Code] ....
View 6 Replies
View Related
Mar 25, 2014
I am stuck with looping columns and rows.What i am trying to do is... I have to loop through columns E3 to G3 and also rows of each column and check for a condition inside the column.How can i achieve that? I have the following structure.. How can i code it?
For i=E3 to G3 'Column loop
for j=4 to 47 'Row loop
'code to check if the value isnumber
next j
next i
View 9 Replies
View Related
Feb 9, 2007
I have got an Excel program that retrieves colunms of data from a Database using MS Query on the first sheet of the workbook. I then have some code that abstracts the data from from the first sheet into each seperate sheet depending on the number value in the Family Groups column. This is all done using a loop.
This all worked fine until I tried to insert another sheet which contains a couple of columns of static values which I dont wish to be affected by the VBA loop. when I now run the code it overwrites the contents of this sheet.
May question is how can I put some code into my loop to prevent it from updating this sheet in the work book?
View 9 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
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
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
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
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
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
Jan 9, 2007
i have a set of data as below and wish to delete ENTIRE ROW if cell is the same but keep the first entry when code comes to it..ie in the data set below excel would keep first row and delete 2nd,3rd,5th, 6,7,8 and then go to next unique identifier which would be AU0000LIFHB3 this would not be deleted as it is unique the would proceed to AU300GPTC011. this process would then stop when no data was available
AU300AUS1019
AU300AUS1019
AU300AUS1019
AU0000LIFHB3
AU300AUS1019
AU300AUS1019
AU300AUS1019
AU300AUS1019
AU300AUS1050
AU300GPTC011
AU300GPTC011
AU300GPTC011
AU300GPTC011
AU300INTE018
AU0000LIFHB3
0
AU300SUNQ027
View 4 Replies
View Related
May 12, 2008
What I am trying to do is to write a macro that will automatically copy six columns from worksheet (Sheet 1) to another worksheet (Sheet 2). i.e. ‘Description of Project’, ‘WBS Code’, ‘Rate’, ‘Employee Name’, ‘Premium’, ‘Invoice’, ‘Status’, ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ from Worksheet (from Sheet 1 to Sheet 2)
The problem arises as I know the names of the columns to be copied in Sheet 1 (as details above) but they can be in any order in sheet 1.
In additional the columns ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ are total columns so when they are copied from ‘Sheet 1’ to ‘Sheet 2’ their values should be copied as opposed to the formulas
View 9 Replies
View Related
May 14, 2013
I have a report that contains several rows of data. This data is separated by page breaks, so for example, there could be 1500 rows, separated into 90 pages by page breaks. Each range may have anywhere between 35 and 45 rows, but some of those rows may contain no data.
I would like to loop through the cells column A, look for a certain criteria, then delete the range around that certain cell that is contained within the page breaks.
I have code that will loop through the cells in column A. I also managed to create code that will delete a set range but since the ranges aren't always a uniform number of rows I need another fix.
View 8 Replies
View Related
Mar 4, 2014
I am trying to run (or loop) through a column of cells, and if cell contains certain text (e.g. ALPHA, BRAVO, CHARLIE) and delete the row if found.
My macro as I run it:
Sub DATA_TEXT ()
'Number of cells to loop through (I am unsure how the .xlend works!)
For i = 1 to 2500
'If function defining the criteria.
If cells (1, i) = "ALPHA" OR "BRAVO" OR "CHARLIE" Then
[Code] .....
View 4 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