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

[Code]....

View 3 Replies


ADVERTISEMENT

Dim Statement Throwing Compile Error

Jun 10, 2014

I attempted to initialize values on a few Dim statements, but got compile errors: "expected end of sentence".

VB:
Dim sumTtl As Currency = 0
Dim searchTxt = "West"

View 4 Replies View Related

VBA Macro Throwing Compile Error

Dec 3, 2013

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.

The code is the following:

Sub Hard_Code_Cells()

On Error Resume Next

lastrow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

[Code] .......

I've been receiving the error message 400 when I trigger the macro and I'm not sure how to correct my code to resolve the issue.

View 8 Replies View Related

When The Sheet Is Protected It Ends Up Throwing A Runtime Error ( 1004 )

May 9, 2006

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............

View 5 Replies View Related

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 View Related

VBA Loop And Delete 15 Rows

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

VBA Code - Loop Through And Delete Rows

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

Delete Rows, Formatting, Loop

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

Loop Over Rows And Delete Row If Value Not In Table

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

Delete Over 3000 Rows In Worksheet Without A Loop

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

Macro To Delete Mutliple Rows On A Loop

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

Loop To Delete Rows If Middle Column Less Than 1

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

Find Data And Delete Remaining Rows (For Each Loop)

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

Delete One Of The Source Rows I Get A #REF Error

Aug 17, 2007

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.

View 9 Replies View Related

Delete Rows Which Contain A Formula Error #VALUE!

Sep 11, 2007

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

View 2 Replies View Related

SUMPRODUCT Formula Throwing Out #VALUES!

Jun 9, 2009

fix cell E8-E19 (totals). I don't think its anything to do with the date format.

View 2 Replies View Related

For Each Loop To Delete Row W/ Value (Loop Backwards)

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

Delete Rows Error "could Not Detect Range"

Apr 13, 2007

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.

View 2 Replies View Related

Do...Loop Error :: Loop Without Do

Dec 17, 2008

Can't seem to figure out why my Do Loop keeps giving me an Error of "Loop without Do".

View 14 Replies View Related

Loop Without Do Error

Jul 24, 2009

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.

View 3 Replies View Related

VBA Error In For Each Loop

Jan 30, 2012

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

View 3 Replies View Related

Loop- Keep Getting Same Error

Sep 4, 2009

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

View 9 Replies View Related

Loop That Keeps Producing Next Without For Error

Mar 24, 2014

I know it has something to do with the way I am trying to complete the loop.

I have tried next i, I have tried if's instead of the do until but can't get it to work.

I want it to cycle through the x and if a value is found then paste into the corresponding i row. If the x value is blank then exit the loop.

View 14 Replies View Related

Prevent Do While Loop From Doing Go To Error

Apr 4, 2014

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 ?

View 3 Replies View Related

For Each Loop Run Time Error

Nov 24, 2008

I have created a macro to calculate data and add it to sheets over sheet 3. It has to do this for any number of sheets above sheet 3.

Below is the code i have created i am just getting an error at runtime which i dont know how to resolve or what i have done wrong

View 14 Replies View Related

Compile Error: Loop Without Do

Jul 20, 2009

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.

View 9 Replies View Related

Getting Error For For Each Loop With If Statement

Nov 9, 2013

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

[Code]....

View 6 Replies View Related

VBA Add Error Handling To A Loop

Jan 17, 2014

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

[Code] ......

View 9 Replies View Related

Sumproduct Loop Error #N/A

Mar 9, 2007

What is the trick for entering the following equation?

=SUMPRODUCT(LOOKUP(B3:F3,{0,0;"A",4;"B",3;"C",2;"D",1;"U",0}))/MAX(1,COUNTA(B3:F3))

When I enter this equation, it yields an #N/A error

Perhaps the { is being entered incorrectly ?

View 9 Replies View Related

Loop Until Reference = Error

Mar 26, 2007

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

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved