Deleting Rows/content Of Rows

Dec 11, 2009

Say I have a method that iterates through a bunch of Sheets. I check the name of every sheet, if it starts with "Data", I need to make everything between A4 and AZ500 empty (either by clearing cells or deleting rows doesn't matter how, as long as the result is an empty sheet below A4).

View 2 Replies


Deleting Rows Based On Cell Content

Oct 17, 2007

I wish to remove the page headers that were imported along with the raw data. Here's the code I've been using.

Column_To_Check = 3
Start_Row = 1
End_Row = ActiveSheet. Cells(Start_Row, Column_To_Check).CurrentRegion.Rows.Count
MsgBox End_Row
Search_String = "."
For Row_Counter = Start_Row To End_Row
If ActiveSheet.Cells(Row_Counter, Column_To_Check).Value <> Search_String Then
Row_Counter = Row_Counter - 1
End If
Next Row_Counter
End Sub

When I import the data from the text file, there are 3 dimensions separated by a period in the same field. I.E: ABC.DEF.GHI and I use the break points to seperate as such: ABC|.|DEF|.|GHI|. Therefore, all rows that contain data also contain a period in cell C, but headers don't.

The Problem

End_Row = ActiveSheet.Cells(Start_Row, Column_To_Check).CurrentRegion.Rows.Count

CurrentRegion only selects up to the first blank row. However, I need this to select the whole sheet, not just the current region.

View 9 Replies View Related

Excel 2003 :: Deleting Or Displaying Rows Based On Cell Content One By One

Mar 20, 2014

I have a company with upto 5 products, and I have created a dropdown box where you select your product.

When I select product 1 i would need info on product 1 , when i select product 2 I need info on product 1 and 2 and continue. so for product 5 -i need info on product 1-5.

I tried to add all info on excel and tried to delete it one by one by deleting rows in excel for products but it does not work.

View 4 Replies View Related

Excel 2010 :: Delete Filtered Rows Without Deleting The Hidden Rows?

Sep 25, 2012

How do I delete filtered rows without deleting the hidden rows in excel 2010?

View 8 Replies View Related

Clear Rows Vs. Deleting Rows To Avoid #REF! Formula Error - Macro

Jun 9, 2009

I have a macro which is copying data from several worksheets into one consolidation worksheet. When determining where to paste the data into the consolidation sheet, the macro includes some logic to find the last row that has data in it (using e.Range("A65536").End(xlUp).Row, where "e" is a variable holding the name of the consolidation worksheet).

Once all the data is on the consolidation worksheet, I have a second worksheet with formulas that link to the consolidation sheet. The issue I have is that the first step of my consolidation macro deletes all data on the consolidation sheet to ensure that no data is double-counted). I am deleting the data with logic that simply deletes all rows from 3 to 65536. Once these rows are deleted, Excel returns a #REF! error on my second worksheet which is linking back to this data.

Rather than deleting the rows on the consolidation sheet, I have tried using the Clear and/or ClearContents commands instead. This works (i.e., my formulas no longer error out), but results in the consolidation macro running very slowly (~15 minutes, compared to

View 9 Replies View Related

Code For Deleting Rows Verses Hiding Rows

May 25, 2013

The first code hides everything just fine based on the date in A1. When I change it to the second code to delete instead of hide it is leaving a bunch of rows that the 1st code hides. Both codes have the same search criteria.

For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Hidden = True
Next cell

For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Delete
Next cell

View 4 Replies View Related

Delete Blank Rows (formula Not Deleting All Rows)

Sep 30, 2008

I have the following codes to delete all blank rows in column A

Dim lastrow As Long
lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
MsgBox lastrow

With Sheet1
For t = 1 To lastrow
If Cells(t, 1) = "" Then
End If
Next t
End With

End Sub

Although it is working , it is not deleting all the blank rows at once, I have to keep pressing on the macro button running the macro several times, until all blank rows are completely deleted.

View 9 Replies View Related

Deleting Rows Macro :: Rows That Don't Contain Word ...

Jan 9, 2008

I got a code to delete all rows in the sheet which contain the word "DETAILS" but I now want to delete all the rows that do not contain the word "DETAILS"

My code if needed is:

Sub Find_details()
Dim rng As Range
Dim what As String
what = "DETAILS"
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
End If
End Sub

View 9 Replies View Related

Filling Rows According To Cell Values And Deleting Rows With No Values

Jan 25, 2013

I have inherited a spreadsheet with over 800 rows (and daily expanding) and 14 columns of data. I've attached a sheet that looks similar, with only 200 rows for reference. Here are my questions:

1. How can I delete all rows that contain no values?
2. I want to fill an entire row of data yellow if the values in the final two cells (L&M) in the row are equal, and red if they aren't. How can I do that?
3. Column A contains only dates, from oldest to newest. I'd like an obvious visual clue for when the months change. Currently it's a long, merged, blue-filled cell that says "March 2011", for instance. It can't be color because all cells need to be filled based on certain criteria (see #2), and borders aren't obvious enough.
4. I want to click on the row number on the very right, but have it select only columns A-M, is that possible?

I'm also having trouble with autofill. It seems to only work on parts of the spreadsheet. I don't know what settings may have been changed, but I do have autocomplete turned on, and no clue what to do despite hours of googling.

Test Tracking Sheet.xlsx‎

View 4 Replies View Related

Delete Rows Based On Content

Jan 5, 2009

Can a macro be used to:

1. Delete rows that contain certain text in a worksheet ?
2. Highlight a cell a colur based on a response ?

I have attached an example of what i mean .....

In the Audit Protocol worksheet is the main information - which contains questions and answers .... If a yes, n/a or no is selected then a response is automatically generated in the observations column (thanks to SHG for helping me with the formula for this) ..... How do I then get the cell to change to red if a NO response only is selected ?

Then the information from the Audit Protocol worksheet (the observation column and number column) are copied across to the worksheet named Action List (this is done just by the copy function) .... How do I go about deleting rows that contain "no action required" - as these are not needed for the report to be generated ?

View 11 Replies View Related

Add (every 3rd Or 4th Depending On Content) Rows Into Columns

May 21, 2012

I have a set of data all in clumn A - I want to sort into into rows - "Name", "Manage" or "Send Message", "Job title/Company" and "Country", but some are missing "Country" (therefore, some entries have 3 rows and other have 4.

The differentiator is that every name is repeated within the cell, whereas no other row is repeated. Any macro to get all these into 4 columns?


Steve Wonder Steve Wonder
Manage General Manager in ZXY games United States Jake Gave Jake Gave
Send message VP Business Development in XYH Games
Russia Carter Jonas Carter Jonas Send message in 148G
Clement Galiay Clement Galiay

View 9 Replies View Related

Select Cell Content From Visible Rows.

Mar 15, 2009

I have a worksheet whereby many of the rows are hidden. These rows were initialy hidden by means of scipts ( I have numerous scripts to hide data under various criteria) eg: Scripts which hide rows based on cell count, cell color, cell data information etc...

Now that I am able to veiw only the information that i want to see.. is it possible to create a script which selects cells only from rows which are visible? I have created a Named range begining from E:12 to G:500 called "Select_EFG"

Uing the example below, how could I select the cell data from the visible rows (12, 30, 34, 35, 50 etc... up to row 500.) from this Named Range (columns E:12, G:500) .... and paste this information in Sheet 2 Row5 columnC


View 2 Replies View Related

Duplicating Rows Depending On Column Content?

Jul 18, 2013

Im replicating rows which have multiple items in Column1. Im aware that the ID Column has source data is like that for now so I wont complicate it just yet.

Once ive got this working I can proceed to the rest of the tasks

View 9 Replies View Related

VBA To Hide Rows Based On Cell Content

Jun 23, 2006

If I have data in range A1:Z99 I want to write a macro to hide all the rows that have a zero in the A column.

View 9 Replies View Related

Clear Content & Formatting Of Hidden Rows

Aug 19, 2009

I am trying to return the range of cells that is not in a range of cells.

Dim rge As range
Set rge = activewindow.visiblerange

'set rge2 = cells not in rge 'HERE IS WHERE I NEED HELP


I am trying to avoid a for each style loop.

View 5 Replies View Related

Move Rows Based On Content Of 2 Cells

Oct 24, 2006

At the moment I have 1 spreadsheet with 12 worksheets that I have to enter data into. I would rather enter all the data onto one sheet then have a macro move the data into the correct worksheet based on the contents of columns A and B.

The raw data will be entered into the worksheet "Data". Once complete, I would like the user to press a button and the macro to then identify from column A the suppliers name. If it is not one of the recognised suppliers, then it would move the row into the worksheet "one off". If it does recognise the supplier then it checks column B to see which of the two supplier's two worksheets it needs to copy it to, with the data entered into the correct column based on the column title (ie only the white columns).

View 9 Replies View Related

VBA And Deleting Rows

Sep 21, 2009

I have an excel spreadsheet that contains about 1000 rows and about 25 columns. The file contains employee information, name, id, cost center, department, title, FTE...etc. Column E contains the cost center which is a 7 digit number (i.e. 8001234). Within the 1000 rows of data there somewhere to 70 cost centers. I would like to delete all rows where a cost center does not match a list of 13 cost centers. I'm thinking I need some kind of array where I type in the 13 cost centers in the code, but I'm unsure of the syntax within VBA.

Also, the second step of the code I'm looking for is to delete all columns except for Column B, E, J and L. Those, by the way are Name, Cost Center, Job Title and FTE.

View 2 Replies View Related

Deleting Rows That Contain Particular Value

Oct 26, 2011

I want to delete all rows in the column of the ActiveCell when the ActiveCell.value < 0.01. Could you tell me why the code below doesn't work? It deletes the right rows but keeps looping without stopping

Sub DeleteZeros()
Dim Col As Long
Dim StopRow As Long
Col = ActiveCell.Column
StopRow = Cells(Rows.Count, Col).End(xlUp).Row


[Code] .........

View 6 Replies View Related

Deleting Rows

Apr 25, 2007

I have a macro that deletes rows. For some reason, the macro bombs out when the selection seems to be too big. Why is that?

deleterow_min = Sheets("Sheet1").Cells(1, 6)
deleterow_max = Sheets("Sheet1").Cells(1, 7)

Rows(deleterow_min & ":" & deleterow_max).Select
Selection.Delete Shift:=xlUp

View 9 Replies View Related

Deleting The All Rows

Aug 5, 2009

In a part of my code I have something that will delete all hidden rows, like:

For Z = 1 To TotalRows - 1
If Cells(TotalRows - Z, 1).EntireRow.Hidden Then
Cells(TotalRows - Z, 1).EntireRow.Delete
End If
Next Z

I've done it in reverse to be quicker, but it's still quite slow. Granted, I am deleting several thousand rows, but surely there's a quicker way? Thinking about it logically I would presume that (in general) selecting stuff first in Excel and then deleting them appears to be quicker, but I'm not sure how to pull that off.

View 9 Replies View Related

Create A Report Printing Rows By The Content Of A Column?

Apr 14, 2014

In the column marked SA I have some numbers. These are Sub-Accounts. I am looking to run off a report that prints off all rows with the respective SA number. I have only included a "4" and a "5" in my example but there is going to be more than one row "assigned" to Sub Account 4, Sub Account 5 etc. Ultimately it provides a break down of the accounts.

View 7 Replies View Related

Deleting Specific Rows..

Feb 21, 2009

Here's my problem. I have the following table:

View 5 Replies View Related

Deleting Multiple Rows?

Mar 15, 2013

I have a large list of coordinates that I pasted from a website, and in Excel it has an emty row between every coordinate. I have about 2,000 cordinates, so ~4,000 rows. Is there a way to delete every other row besides manually?

View 1 Replies View Related

Deleting Rows Based On Their Value

Jun 26, 2008

I am having trouble with the .SpecialCells(xlCellTypeBlanks)

What I need to do is for a macro to first go through a range ("G8:G50"), Add a 0 value to any blank cell.

Then I want the macro to delete the entire row for any of the cells that have a value of 0 in that range of "G8:G50"

View 9 Replies View Related

Deleting Specific Rows ....

Nov 9, 2008

I have created a worksheet (through an import into MS Excel 2007) which contains 287,281 rows. However, the data I need is located in rows: 4, 67, 130, ... (or n+63) rows.

View 10 Replies View Related

Deleting Rows With Macro

Mar 19, 2009

I want to delete every second row, because I have data with 0.25m interval and I want 0.5m interval. Why I try the following it doesn't work?

View 2 Replies View Related

Loop For Deleting Rows

Apr 20, 2009

Loop for Deleting Rows
Whats wrong with my code ?

View 3 Replies View Related

Deleting Rows On Conditions

Apr 24, 2009

I would like to delete rows that are based on these conditions: First ,Do a loop from row 2 to last available row. - Delete rows with same column(column B) that has the same value. However I want the last available row with the same ID to remain.

- Delete Rows with any values in found in other sheet column B. When the value taken from the first sheet(ABC) is compared to the column B in second sheet(DEF), if they are equal, the row will be deleted in the second sheet and the rows in first sheet will main. I had attached an simple example with the 2 sheets. The Result are shown in the example.

View 2 Replies View Related

Deleting Unwanted Rows <10

Aug 24, 2009

I need some VBA to delete rows that are under the value of 10 in column H and keep the rest, is there a way of doing this

View 7 Replies View Related

Deleting Duplicate Rows

Oct 14, 2009

In my Excel 2003 worksheet I need a macro to search column B for duplicate entries. The data in column B is both numeric and string. If there are any duplicate entries, then I need the duplicate rows to be deleted.

View 4 Replies View Related

Copyrights 2005-15, All rights reserved