Delete 5 Rows With Loop Without Throwing Up Error?
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
Basically I want the macro to look through a column of values and if any cell in that column has the value 'LOCK' the macro would hardcode 'n' into the adjacent cell. Additionally, because the column in which the LOCK cells would appear is part of a vlookup, it returns #N/A which I would like my macro to ignore and then continue to evaluate the next cell all the way to the end of the column.
I am currently working on a project which at various times does 2 different things. These things are to be done on a "PROTECTED" (UserInterfaceOnly:=True ) worksheet.
The first is a Range.Replace command. The other is a Hyperlinks.Add command.
Now when I'm testing ( typically the worksheet is not protected ) everything works like a champ. But as soon as I protect the sheet the following scenario happens.
1. The replace DOES NOT work. 2. The hypelink.add DOES NOT work.
when the sheet is protected it ends up throwing a runtime error ( 1004 ) "Protected Sheet error" the real odd thing about this is that it doesn't throw the error on the command itself. On the hyperlink the error happens about 3 command further along in the flow............
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.
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
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
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.
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?
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:
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 ¬¬¬¬¬¬¬¬¬¬¬¬¬
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"
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.
I have two worksheets that directly link a range of cells on both sheets, ie: Output!a1 = Database!a1.
In this case, the Database sheet contains data that I want to manipulate by deleting entries that are defined in rows. however, when I delete one of the source rows I get a #REF error on the Output sheet.
Is there any way to avoid this so the entries will automatically shift down a row if a row has been deleted? :o
So if i were to delete row two on the source page and the data from row three moves down, that would, in turn, cause the row three data on the output page to move down one row as well.
After performing several operations and sorting the results I end up with a lot if #VALUE! errors in colum B. I would like to search thru those results and delete any row containg "VALUE! in colum B
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
When I run it the message "could not detect range" comes up. I selected my desired range then ran the macro and did not get the desired results. It looked like maybe only one row was deleted. My data has seemingly random blank rows between it, some small row ranges, some very large.
I have cobbled together some code that I hoped would do the following:
Sort data Copy all data from one person and put it on a sheet Create a pivot on that data populate another sheet based on that persons data Save as a PDF Clear the data and start again for the next employee
I have copied this from other macros I have used in the past. Up until now the parts I have been working on have been ok. however when I try and run it now I get a "Loop Without Do" error. I really can't figure out why.
I'm trying to do a for each loop in a row, I've tried to drill it down to be as simple as possible, but I keep getting the Run-time error '424': Object Required
I've tried to make it simple, with just inserting a 1 into the cells, still get the error.
Code: Dim firstRow As Range Dim rCell As Range Set firsRow = Worksheets("MAIN").Range("F3", "AG3") For Each rCell In firstRow 'error shows here rCell.Value = 1 Next rCell
I need my macros to search for the word "Cancel" in columns "T". Once found, I need the macros to make that entire row a certain pattern ( .Pattern = xlGray16)
There will be other wording in these cells that contain "Cancel" or "Cancelled".
A compile error keeps occurring. It says "Ambiguous name detected: FormatCell"..
Sub DoCancelCells()
Dim strSearchString As String Dim wksSheet As Worksheet Dim rngSearchRange As Range Dim intSearchCount As Integer
I've got the following code which opens/gets the excel files from a specified directory and imports them into an open workbook. I've added a GoTo Error before my loop which checks if there are any files in the directory and shows an error message if it's the case. Problem is since the loop goes on till there are no more files in the directory, it goes back to the error. How to change that ?
VBA but familiar with other programming languages.
I have looked through previous posts with the same problem and I see that most people forget to add the "endIf" before looping. From what I can tell, I have ended all of my "If" statements.
The goal of this macro is to take temporary data and finding a match in other sheets and copying from "Temp" and pasting into the other sheets (possibly in the first blank cell, depending on the case) then deleting the row and moving on to the next row and repeating the process until "Temp" is empty.
I am getting the Run-time error '380'. Could not set the value propery. Invalid property value.
I have a user form for data entry purpose which is working fine. Now, I am making a code for data editing purpose which pulls out data from the worksheet into the user form.
I am able to do this for text boxes and combo boxes that are not linked with each other. However I am getting a problem in the following case; I have a combo box and a text box that are linked to each other by combo box _change code which is below which may be the reason for the error I am getting for the next code...
Code: Private Sub cmbColor_Change() If cmbColor.Value = "Other" Then lblColor.Visible = True
My current code opens a file ("Report01.CSV") from the directories in column A (one at a time), and copies data into sheet 2.
The problem is that sometimes there is no ("Report01.CSV") file in one or more of the directories.
What I would like to add is an error handler that would remove the directory (delete that cell) that caused the error and continue with the loop. I.e. resume importing data from the other directories.
CODE: Dim wrkMyWorkBook As Workbook Dim lngRow As Long: lngRow = 1 Dim lngColumn As Long: lngColumn = 2
Do Until Sheets("Sheet1").Range("A" & lngRow).Value = vbNullString Set wrkMyWorkBook = Workbooks.Open(Filename:=Sheets("Sheet1").Range("A" & lngRow).Value & "" & "REPORT01.CSV") lngRow = lngRow + 1
I'm trying to create a simply loop for an easy task of copying and then pasting a formula into other cells. Basically I'm trying to get this script to run until there is no more "slsperson" references to go to.
Sub z_Paste_Sales_Formula() Dim i As Long Dim icolumn As Integer icolumn = 4 Cells(3, icolumn).Select Selection.Copy i = 1 Do Application.Goto Reference:="slsperson" & i ActiveCell.Offset(1, 0).Select sls_row = ActiveCell.Row ActiveCell.Offset(1, 0).Select gp_row = ActiveCell.Row Cells(sls_row, icolumn).Select ActiveSheet.Paste Number = i + 1 Loop Until Application.Goto Reference:="slsperson" & number = error End Sub
