Excel VBA Code To Select A Range (rows) Based On Values And Delete

Aug 8, 2013

I am trying out with a code which checks for cell value as "Select" in column IU and then checks for corresponding column IV for value as "0". Please note that "Select" and "0" are populated by formulas. I need the select "Select" and "0" till the next "Select" occurs in column IU and delete the selected range and continue the process until last non empty cell based on column C.

I have written the below code but it doesn't work.

Code:
Public Sub Test()
Dim nRow As Long
Dim nStart As Long

[Code]....

I could have uploaded the excel file that I am working on but did not find any upload attachment option.

View 1 Replies


ADVERTISEMENT

Excel 2007 :: VBA Code To Delete Rows Based On Text Starting Content

Oct 25, 2013

Using excel 2007. I am interested in writing a VBA code to delete rows based on the text starting content. I would like to delete rows with cells that do NOT start with an "S" or "SA"

EX:

05S0128
06S0112
05S2298
S25852
S36963
SA36185

I would only like to keep the last 3 lines.

View 3 Replies View Related

Excel 2010 :: Delete Duplicate Rows Based On Values Of Cells

Jun 17, 2014

I'm new to VBA and macros, using Excel 2010, and am trying to figure out how to delete all duplicate rows in a sheet where 2 or less of their values in column A is "1". I'd like have a script that is flexible enough to change to 3 or less if need be. I also have a header row that needs to be offset in the process.

A---B-
0--123 <-delete
0--123 <-delete
0--123 <-delete
1--123 <-delete based on this the value of column A
0--123 <-delete
0--123 <-delete
1--321
1--321
1--321
1--321
1--321

or

A---B-
0--123 <-delete
0--123 <-delete
1--123 <-delete
1--123 <-delete based on this the value of column A
0--123 <-delete
0--123 <-delete
1--321
1--321
1--321
1--321
1--321

View 5 Replies View Related

Code To Delete Rows Based On Status Code In Column

Jul 24, 2009

I'm trying to write a VBA script which will delete all rows in my Excel spreadsheet where Column I (which contains a status code) does not contain the word "Completed".

At the moment, I'm doing this the other way round: my script is able to search for entries in Column I which contain the status codes "Pending", "Awaiting Authorisation", "In Progress" etc and delete them. The idea is that when all those rows are deleted, I'll only be left with rows which have a status of "Completed". This works fine at the moment. However, the concern is that if a brand new status code is added to the data file, my script would be unable to pick it up and delete it. This is a small sample of the code I'm currently using (which deletes all the rows with statuses other than Completed):

View 4 Replies View Related

Code To Delete Rows Based On Condition

Apr 24, 2009

I have an Excel spreadsheet that is given to me weekly, but I'd like to remove repetitive and empty rows before presenting it to someone. It is set up like this:

Row 1, A1, contains the word Project.
Row 2 is blank
Row 3 is blank
Row 4 is blank
Row 5, A5, contains the word Organization.
Row 6 is blank
Row 7, A7, contains a 6-digit number starting with 3.
Row 8 is blank

(all of the above starts over again (loops) approximately 30 times)
The final row contains the phrase "Grand Total"

*Row 5 is repetitive and is not required. I'd like to delete it.

I have too many empty columns. Getting rid of them (and Row 5) would greatly shorten my spreadsheet.
I am aiming for:
Project
301111

Project
301112

Project
301110
(Repeat until finished)

Grand Total:

View 4 Replies View Related

Code To Delete Rows Based On User Input

Aug 21, 2008

I am using the following piece of code to delete unwanted rows from a worksheet:

Sub DeleteDates()

FinalRow = Cells(65536, 3).End(xlUp).Row
For i = FinalRow To 1 Step -1
If Cells(i, 3).Value Like "*2007*"
Cells(i, 1).EntireRow.Delete
End If

Next i

End Sub

What I am working with is a sheet of about 5000 customer appointments going back to the beginning of 2006. A have peiced together a lot of code to format it exactly as I want and to create a pivot table of what is remaining after the rubbish has been deleted.

However I am finding that I need to keep amending and re-running this bit of code, depending on the date range I want to look at. For example the code above will delete all appointments made in 2007.

My question is: is there a way that I could incorporate an input box, whereby excel asks the user for a start date and an end date and deletes any rows outwith that range?

Some important info: All of the dates are in column C, in the format mmm yyyy

I would only need to narrow down to a month - so for example mar 2008 - jun 2008

View 9 Replies View Related

Delete Rows Based On Values

Nov 10, 2008

I have a 20,000 row text file that I import. It has blank rows, page headers and footers, and many rows at the bottom that I do not want. I have been cleaning up the data manually but this takes a great deal of time and I have a need to perform this clean up on a more frequent basis.

I have tried recording a macro to do some of the deleting but I am running into an issue that I do not know how to over come. When I filtered the data based on "Blanks" on a specific column and then try to delete them in mass, Excel errors out stating that my request was too complex.

View 11 Replies View Related

Modify And Enhance Recorded Code - Delete Rows Based On Value

Aug 22, 2012

I searched many sites and used codes which delete Rows based on criteria. In my case those codes works, BUT it took so much time about 30 min since there is about 75 thsd rows, and that solution in not time saver. I recorded code below and it is done in seconds. make it dynamic, VBA, since number of rows is every month larger. Basicly, I need code which delete rows based on Column B, where walue is "R"

Code:
Sub DelRowsBasedOnOneCriteria()
'
' DelRowsBasedOnOneCriteria Macro

[Code]....

View 4 Replies View Related

Delete Rows Based On Values In Rows Below

Jun 20, 2008

I am copying a price list from a worksheet. I currently have a script that deletes unwanted rows (products) but these products' header rows' are left. I also want to delete these text based headers. One solution might be a script that reads a columns cell value in the row(s) below and if values are missing the header row should be deleted.

View 2 Replies View Related

Macro To Delete Rows With A Range For Values

Mar 28, 2009

I need some assistance with a simple macro. Im clueless when it comes to macros.

Heres what I have:

column "e" contains the year built for homes. Some of the fields contain "9999" or "0000" or blank. I need to delete these entire rows. If the year is between 1900 and 2020 I would consider it valid and keep the row.

So I need a macro that says something like this:

if column "e" is not between 1900 and 2020 delete the entire row.

View 11 Replies View Related

Delete Rows With Values Between Numeric Range

Apr 16, 2008

I want to delete all the rows with values between -1 and 1 in column 'I'. I currently set up a macro to do the formatting and conditional stuff that highlights all values above 1 and below -1 - these are the values I need to conduct my analysis on, but I can't figure out how to delete the rows in between. The worksheet has approximately 5,000 rows (and growing) and this would help me clean up the data significantly.

View 7 Replies View Related

Excel Function To Select Two Columns Based On Range?

Jul 30, 2014

Function to select two columns based on the header and the variable given.

Sample excel file attached for your ref.

Excel sample.JPG

Find the excel file in which I am looking for an formula which will look up variable in Col A for Eg USD and search the same in Row 1 and then will select COL D:E and so forth for other currency.

View 4 Replies View Related

Delete Duplicate Rows Based On Cell/column Values?

Oct 28, 2009

I have a excel file which contains dublicate rows. The duplicate rows can be identified based on few cell/column values. I need a macro to delete the duplicate rows when the below condition is satisfied: let us consider row 5 and row 6:

If column 7,12,13,16,17,18,19,23,24,27,28,29,30 in row 5 = row 6 then row 6 has to be deleted. This condition has to be followed for all other rows in the excel used range. Have attached the sample workbook.

View 5 Replies View Related

Excel 2010 :: VBA Code To Search Multiple Columns And Delete Any Duplicate Cells (not Rows)

Jun 12, 2014

My Excel program (Excel 2010) currently has several columns and each column looks for and pulls data from a specific file on my computer. Then I need to delete any duplicate data entries, count the number of unique entries and track the changes through a chart. I have everything done except I cannot figure out (or find on the internet) a way to search in multiple columns (more than 2) and delete just the duplicate cells. I want to delete the cells in a way where there is one left. For example if the code 12gf is duplicated three time, I want to be left with one 12gf (it doesnt matter what column the original one is left in). Additionally, column length changes and they are not sorted. I have attempted to attach an image of an example file below.

View 14 Replies View Related

Excel 2010 :: How To Delete Rows Based On Cell Value

Sep 23, 2011

I have a massive spreadsheet (ca.110 000 rows [excel 2010). I need to cut this monster by deleting all the cells that do not have a certain cell value within them.

Say that in my column A there are different entries (text type). I would like to delete the ones that do not match the following criteria:

"mytextascriteria*", where * somehow stands for the rest of the string.

Strictly speaking I can only determine beggining of the string I want to perserve but the values at the end of that string are changing.

I found the thread about deleting entire row based on a cell value here:
Delete entire row based on one cell?

but I couldn't quite work out how to apply it in my scenario....

Otherwise is completely pointless to do it manualy with these amout of records.

View 5 Replies View Related

Add Or Delete Rows From Multiple Sheets Based Table Range

Aug 14, 2009

I have a need to add or delete rows from a number of known sheet names using a table of variables on another sheet that tell me the start row of the sheet I need to go to and the number of rows I need to either add (ie copy rows and paste / insert these) or delete (delete rows).

There are multiple blocks of data I mey need to amend on each sheet and the values in my table of variables will change on each iteration (ie if I delete rows from the first block on a sheet, the start row for the 2nd block I need to amend will need to be updated in the table of variables before I can edit the 2nd block on that sheet).

I have been able to get the process to work for a single instance (ie one sheet and amendments to the first block of that sheet) but I can't figure out how to create the loop to elegantly move to the next set of variables and repeat the process for the 2nd, 3rd, 4th block etc on the first sheet and then move to the 2nd sheet to repeat the process etc.

Public Sub EditCurrentBlock()
Dim rowcount As Integer
Dim startrow As Integer
Dim endrow As Integer
Dim rowcountBal As Integer
'Dim selSheet As Worksheet (tried to use this to nominate the sheet variable but
' had problems so scrapped it)

'Reconfigure the GP Revenue block.................

View 2 Replies View Related

Hide Range Of Rows Based On Cell Values

Aug 13, 2014

I've been trying to put together some code to hide a range of rows on one page based on cell values on a different page. So basically Sheet1 cell A1 value is 10, A2 value is 20. When macro is run via button rows on Sheet2 from 10-20 would be hidden. The issue is the values in the cells on Sheet1 will vary. I thought something like the following would work but it doesn't.

[Code] ....

View 6 Replies View Related

Excel 2007 :: Macro To Insert Rows Based On Cell Values

Dec 19, 2013

a macro to insert rows based on certain cell values in column A.

I have uto 300 rows of data. Below is an example of column A.

R1
1
2
3
4
5
6
7

[Code]...

If (above the R) is an 8, I need to insert 2 rows above that R and directly below the 8.

If (above the R) is a 9, I need to insert 1 row above that R below, directly below the 9.

(Below the R there is always a minimum of 8 digits with the 9 and 10 being random).

I have excel 2007

View 5 Replies View Related

Excel 2010 :: Macro To Color Code Cells Based On Value In A Cell And Range In A Table

Dec 2, 2013

I am using Windows 7 and Excel 2010.

Is there a way to create a macro to color code a cell based on the value in a cell, and then look up a value in a table, then color code it based on where it fits into the table?

I have a table of values for about 30 projects. In column g - there is a CPI value (see bold column)

Example: Project ID
Name
Program
PMT
SI ID
AC
Milestone
TCP Level
[Code] ......

Here is the table:

I have to color code a cell, base on the CPI and how it fits into the table below. So if the current Milestone is M2 or M3 and the CPI calculated is .14 the cell would be colored RED, if the CPI number is 2.01 for M2-M3 I would want cell to be colored Turquiose. If we were at Milestone M6 and the CPI was 2.01, it would be colored blue. If the CPI was .75 at Milestone M5, it would be colored Green

LEGEND
Earned Value Limits

Milestone
RED
Yellow
Green
Turquoise
Blue

M2-M3
2.15

M4-M6
1.66

M7-M11
1.26

View 2 Replies View Related

Adding Items To Array From And Excel Range Based On Values?

Oct 2, 2012

On this is a column of Categories and a Column of sizes. I want to use these in a user form. The user will select their Category from a drop down list and the second drop down list will include only the sizes that appear next to the chosen category So for example in the attachment if the user chose 'AUD' as a category they would get the size choices of, '2x4 insert', '2x4 replica' and 'A4L' in the other drop down menu.

The master list of sizes will have to remain on a worksheet as this is what other operators will amend from time to time.

View 1 Replies View Related

Multiple Listbox Select Followed By Delete Rows Of Slected

Apr 24, 2007

what is the vba code to delete the rows that the data you select from a list box. also how do you multiple select the data. the code i used is this

Sub RemoveDuplicate() Dim AllCells As Range, Cell As Range Dim NoDupes As New Collection Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item

' The items are in A1:A105 Set AllCells = Range("p21105")

' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.

On Error Resume Next For Each Cell In AllCells NoDupes.Add Cell.Value, CStr(Cell.Value) Note: the 2nd argument (key) for the Add method must be a string Next Cell................

View 4 Replies View Related

Excel 2007 :: Cell Color Change Based On Values (Range)

Oct 2, 2011

How to change the background cell color based on value ranges(s)

I tried conditional formatting but it works between two values only, in my assignment I want to show:

River levels in relation to flood class
>=2m =2.6 =3m major flood (background turns red)

I hope it is possible in Office 2007

View 9 Replies View Related

Select All The Blanks In A Range And Delete Them?

Feb 25, 2014

I have data that gets seperated into different cells and what i need is for all the data to line up from left to right with no empty cells in between.. so basically shift delete on each cell and shift the data left.

So i thought i know, ill simply f5, select the blanks, delete and shift left... however that option doesnt come up.. it simple says delete row?

What is the best way to select all the blanks in a range and delete them and have the cells that contain data move to the left so all data is next to each other.

View 1 Replies View Related

VBA Code To Select All Values Except 0 In A Filter

Jun 25, 2013

I have a workbook and have created a filter across range A1 - BU1. I want to filter column BQ. In this column I want to use the filter to show all values except for 0.

View 2 Replies View Related

Select Column C And Delete Blank Entire Rows Till C300

May 4, 2014

i want to delete entire blank row from column C2:C300 i mean if i have data c2:c100 then c101:c300 delete entire blank rows

View 4 Replies View Related

VB Code To Select All Rows With Data In A Column

Dec 3, 2008

In my macro, I need code that will select all the rows that contain data in a given column, minus the first row which has a header.

View 6 Replies View Related

Select Data Rows For Pivot Table Code

Dec 31, 2006

I have worksheet with data . Everytime number of rows with some data in it changes . Like sometime there are 100 rows with data or other tome it may be 57 only. I want to write VB code to select only those rows which have data. Thereafter I will use Pivot Table code to get required information. I know VB code to generate Pivot Code. But what happens is that if any blank row gets selected in data range for Pivot Tables, thereafter grouping by "Month" is not done.

go through attached file? In Module 1 ( which contains macro ) instead of R1C1:R8C4 , I want range with data only.

View 3 Replies View Related

Code To Select More Than One Range At A Time?

Feb 1, 2012

I thought i would create some code to highlight a batch of cells so i can modify formatting all together rather than cell by cell

I am unable to get the generated code to select all ranges together, it only shows the last range

Here is my code

Code:
Sub SelectRanges()
'
' Macro1 Macro
'
'
Range("D12:H12").Select

[Code]....

View 7 Replies View Related

Code To Select Actual Range

Feb 25, 2009

I have created the following code by recording a macro. The ActiveCells (“A1.A5203”) were based upon the actual number of records at that time. I wish for this macro to be used in a template and the actual number of records will vary from time to time. What do I need to add to this code to allow the AutoFill function to operate and fill only the actual number of records that exist.

ActiveCell.FormulaR1C1 = "=LEN(RC[-2])"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A5203")
ActiveCell.Range("A1:A5203").Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=8,""0""&RC[-3],RC[-3])"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A5203")
ActiveCell.Range("A1:A5203").Select

View 9 Replies View Related

Select Range Macro Code

Apr 17, 2008

I have made a sheet where the user can insert/delete rows in a certain intervall of rows. The upper row is 17, that is I have named the cell "First" and then the last row "Last" and then I am using integers:

Dim intStartrow As Integer
Dim intLastrow As Integer
intStartrow = ActiveSheet.Range("First").Row + 1
intLastrow = ActiveSheet.Range("Last").Row - 1

So it is only possible to add/delete rows if the markerer is in between the above rows.

Now I would like to select the rows, from first to last. Since the rows are never the same, users add and delete rows all the time, I can not use

Range("A17:Z49").Select

Can I somehow make a range selection using the Integers, somethimg like

With ActiveSheet
Selection."intStartrow:intLastrow"
End With

View 3 Replies View Related







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