Delete Rows On Multiple Sheets Below

Apr 22, 2007

One more short macro code I would like to have.

I would like to delete rows on all worksheets in a workbook below where the value "Total" exists in column B (same on all sheets).

View 9 Replies


ADVERTISEMENT

Delete Rows Based On Value In Multiple Sheets?

Apr 2, 2014

I have the following code to delete rows based on the value "No" in cloumn "L". This code should loop through all sheets and delete the corresponding rows.

[Code]....

The code works perfectly but with one little issue. It fails in the first run always with the following error message.

VBA error.png

I need to comment out the line

[Code] ....

continue it with a breakpoint to "End With", remove the comment. Then it loops all worksheets and deletes the rows.

There is no protection or similiar on any sheet.

View 1 Replies View Related

Macro Button To Add / Delete Rows Across Multiple Sheets

Jul 24, 2014

I've been piecing a macro together from different sites and have run into a couple issues I can't find resolutions to. I have a excel doc for tracking paint emissions per week. I would like to have a button on my master tab that will add a row and fill the formatting from the line above across all 60 some sheets. Ideally I would like the same button to first prompt if you want to add or delete rows. When adding rows I would like it to add it above the button (there's 3 buttons and for 3 different sections) and if deleting ask which row to delete. If it can't be done in one button that's fine, I can live with that. The big thing I need is for it to replicate across all of the excel sheets. I've attached a copy of the document

View 1 Replies View Related

How To Delete Rows With Strikethrough On Column A Among Multiple Sheets

Nov 24, 2013

I have a workbook with multiple sheets. I want to use VBA macro to delete the row where the first column is strikethrough. I try to the follow VBA code but it doesn't work.

Sub Macro1()
Dim cell As Range
Dim delRange As Range

[Code]....

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

Delete Rows Of Other Sheets Also

Jun 23, 2007

I have a WB where the first sheet is "Introduction", second is "Master Data" wherein the name of players with some details are kept.

After that some sheets are there which are linked to "Master Data" ie say Sheet2,sheet3,Sheet5 (sheet 4 is not linked to "Master data" but appears after sheet3)

I want that if a record , say record 3 at row 5 is deleted also delete that record 3 in other linked sheets ie Sheet2,sheet3,Sheet5 The positions of the records differ from sheet to sheet ie if record 3 is at row 5 of master sheet it coulsd be on row 8 of sheet2, at row 10 of sheet3 & at row 6 of sheet 5. All the name of the person are linked to sheets with =masterdata!B5 like this.

View 9 Replies View Related

Paste And Delete In Multiple Sheets

Feb 21, 2007

I have 3 command buttons that I would like to do the following.

1 - I need to change the macro below to add the record to multiple sheets. (I've been told how to do it but I guess I just didnt hold it in).

Sub Copy()
Dim rng, dest As Range
Worksheets("sheet1").Activate
Set rng = Range("A1:J1")
rng.Copy
Worksheets("sheet2").Activate
Set dest = Cells(Rows.Count, "A:J").End(xlUp).Offset(1, 0)
dest.PasteSpecial Transpose:=True
Application.CutCopyMode = False
End Sub

2 - How can I change this code to find the record by enetring the First and Last name (A1 and B1) click on the command button and some how getting all the fields for sheet2 (A:J) in sheet1 for that record.

3 - Change the code to delete the record (A1:J1) from all the sheets.

View 2 Replies View Related

Delete From Found Value Downwards On Multiple Sheets

Dec 19, 2007

error in my code, its the final part of the jigsaw for me to complete my project.

I am trying to find a variable that the user types in my spreadsheet and delete columns from several worksheets. I'm really close but keep getting the above error.

Here is the code.

Sub DeleteEmp()

Dim RemoveEmp As String

''' CURRENTLY DOES NOT WORK FOR SOME UNKNOWN STUPID REASON

MsgBox "Warning. This process will result in the permanent removal of an employees datails"

FinalWarning = MsgBox("Should I continue?", vbYesNo Or vbDefaultButton2, "Confirmation")

If FinalWarning = vbNo Then

View 4 Replies View Related

How To Allow Users To Add / Delete Rows / Columns In Sheets With Macros

Mar 11, 2014

I have a sheet with macros that allows users to fill in data from columns F10 to O10 from row 10 to 54.

Column P will then make some computations based on the inputs from col F to O.
Row 55 will also make some computation based on inputs from row 10 to 54.

How to I modify the code below to allow users to either add/delete columns and rows such that it does not fowl up the computations?

The computation does not have to be at column P or at row 55, they can be dynamic and change depending on how many rows and columns the users delete/add.

A little background on what the code does. From Column F to O, the code UNLOCKS the columns only if user inputs date in Cell 3 of that particular column.

Example. Column F is locked from F10 to infinity at the start. Only when user inputs date into F3 does the cells F10:F54 is unlocked for user to input data.

This goes on until column O. I want users to be able to add more columns, or delete ones they don't need.

View 2 Replies View Related

Find Value On Multiple Sheets & Delete Corresponding Range

Jun 11, 2008

I'm building a course registration workbook and am working on the unregistration macro.

I have a seperate worksheet for each course, labelled by the course code.

There is also a page for each registered participant that shows what courses they're in.

The macro reads off the information on the participants page, and then should goto each registered course's page, look in column C for their name, find it, delete the contents of that row for Columns C to G (has their other information) and then shift all CELLS (not rows) below the now deleted cells up 1 to keep track of the order they were added.

I've tried recording my own macro and editing... but it doesn't work very well for this.
I've also tried searching and come up with nothing, but if someone can look at my code, and/or point me in the right direction, it would be greatly appreciated.

For Each ws In Worksheets
'checks only course sheets that match the courses the individual is registered in.
If ws.Name = unregisterCourse1 Or ws.Name = unregisterCourse2 Or ws.Name = unregisterCourse3 Or ws.Name = unregisterCourse4 Or ws.Name = unregisterCourse5 Then

'Should search for the variable "unRegister" in column C and select it.
Cells.Find(What:=unRegister, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select

'Now needs to select the four cells to the right as well (C:G) and delete them

'Select all cells below from C:G and shift them up one.
End If
Next

View 5 Replies View Related

Macro To Delete If Set Of Multiple Rows Equals Another Set Of Rows

May 10, 2012

I need a macro that can look at multiple (say three) rows and delete those rows if they match another three rows in the worksheet.

For example:

1 0
3 1
5 7
4 4
6 5
8 3
1 8
5 2
3 9
6 5
8 3
1 8
7 5

If three rows are the same, then one set is deleted (it can be either bottom or top set). I would like to do this for rows 1-500 in the worksheet.

View 1 Replies View Related

Delete Rows Where Multiple Column Meets Multiple Criteria

Sep 29, 2011

Need to create a macro?

Delete rows where multiple column meets multiple criteria.

detail:
delete rows where
Column H is less than 10000
AND
Column C is empty(blank)

Those 2 criteria have to occur at the same time..

View 1 Replies View Related

Delete Only Cell Values (not Formulas) In Multiple Sheets

Nov 3, 2009

I am trying to make a button that will re-initialize the workbook: clear (delete) all unlocked cell's values. I have the following

View 5 Replies View Related

Delete Multiple Rows :: Match In Multiple Columns

Oct 29, 2008

I need to find all rows that have columns that match in all 3 of the columns.

I then need to delete all but the last row in each "group" of rows.

like:

1 q w e
2 q w r
3 q w r
4 q w r

Delete rows 2+3

View 9 Replies View Related

Macro To Split Data Into Three Different Sheets / Tabs - Delete Blank Rows

Jul 30, 2013

I'm pasting a lot of data into a spreadsheet and then using a macro to split the data into three different sheets/tabs. I'm doing this by simply copying entire columns. Each sheet/tab has a headings row and autofilters added so that my boss can filter on certain manufacturers in one of the columns.

The problem is that when he selects a manufacturer, at the bottom left corner of the screen it shows the number of records, which at the moment will be something like "6 of 65211".

The actual sheet only has a few hundred rows (not 65211). So how do I delete all the blank rows beneath my data using a macro or vba code?

As an extra point, I'd like to use column B to check for blank rows (not column A).

View 6 Replies View Related

Cycle Through Multiple Rows On Multiple Sheets?

Mar 11, 2014

Data starts on row 4 of each sheet except for the "Labels" sheet where data is pasted.

I need to skip the sheet if there is no data supplied in it and move to the next sheet.

View 1 Replies View Related

Delete Rows With Multiple Criteria

Sep 8, 2009

i have several student ID results that have conditional formatting in place. I'd like to KEEP the following data but delete/hide everything else:

1) Filter out purple rows
2) WITH filtered rows: KEEP any scoreA that's 100% or less than 85%
3) Look at Score A and Score B - KEEP if the difference is greater/less than 5%
4) If a student took testB but didnt take testA, KEEP.

View 2 Replies View Related

Delete Rows, Multiple Conditions

Jul 20, 2009

I need help with the following conditions. I have attached a sample file with desired results.
Conditions required to delete row:

1Delete Row when cell E is blank (example: Row 6 will be deleted as E6 is blank)
2First character in column 'C' is an alphabet (for example: Row 5 will be deleted as the first character in the cell is an alphabet
3First character in column 'B' is an alphabet (for example: Row 29 will be deleted as the first character in the cell is an alphabet

Special Notes : All characters in column 'B' are not in number format (ex: 555-9999 is general)
There are more than 1500 rows of data that need to be sorted with the above conditions.

View 9 Replies View Related

Delete Rows With Multiple Space

Nov 16, 2009

I need a macro to delete rows with spaces greater than 20. I don't know how excel define a space so I'm not sure where to start.

View 4 Replies View Related

Delete Contents From Multiple Rows

Dec 29, 2012

I'm trying to put together functionality which allows the user to remove cell content within a selected row. I've chosen to remove the cell content rather than to delete the whole row because I need to maintain the 'Input Range'.

The code below is the script which I've put together to clear the cell contents.

Code:
Sub DelRow()
Dim msg
Sheets("Input").Protect "password", UserInterFaceOnly:=True
Application.EnableCancelKey = xlDisabled
Application.EnableEvents = False
msg = MsgBox("Are you sure you want to delete this row?", vbYesNo)

[Code] ........

The code works fine if the user selects one row. However, if the user selects mutliple rows, although the text values are removed from all rows, only the 'Interior Color' is removed from the first rather than all and I'm not sure why.

View 3 Replies View Related

Delete Multiple Rows Under Certain Conditions

Aug 1, 2008

I need to eliminate the passing jobs, leaving the failing ones so I can do coaching sessions with the guys that are missing the mark.

Conventions of the report:

Each job has a 20 digit job # and is allocated 12 rows worth of information fields unless customer has more than 1 cable outlet with a modem, then the rows increase by 4 for each additional modem
Tech name is repeated after every 12 rows even if within the same job #
On passing jobs, column I will only contain “Yes” (there are blank cells in column I, as not every parameter is checked on every job), failing jobs will contain “No” in column I

Items I need to accomplish:

· Increase column C width to 22 and column E to 25

· Format column C to number, 0 decimal places (to avoid scientific notation)

· Move tech name to beginning of each job # and eliminate repeated names

· Delete all rows plus tech name on passing jobs

· Change cell color to red in rows (columns A-I) that contain “No” in column I

I have highlighted each different job # in different color


John Smith10002934422610200000Outlet 2D - Up Stream SNR274029.4Yes10002934422610200000Outlet 2D - Up Stream Tx365546Yes10002934422610200000Outlet 2D - Down Stream .............................

View 10 Replies View Related

VBA To Insert And Delete Multiple Rows

May 30, 2009

Does anyone have codes to insert and delete multiple rows. I need to run a macro where a dialog box pops up requesting number of rows to insert and delete.

View 9 Replies View Related

Insert Rows On Multiple Sheets?

Apr 2, 2008

I need to figure out a way to automatically insert rows and copy data on multiple selected sheets. for example, if i insert a row anywhere on sheet 3, i need that same row inserted in the same location with the cells populated with the same data on sheets 4, 6 and 9.

View 9 Replies View Related

Unhide Rows In Multiple Sheets?

Oct 12, 2011

Is it possible to unhide a row in one sheet and the same line can be un hidden automatically ini 2 other sheets within the same workbook

View 1 Replies View Related

Insert Rows On Multiple Sheets

Apr 2, 2008

way to automatically insert rows and copy data on multiple selected sheets. for example, if i insert a row anywhere on sheet 3, i need that same row inserted in the same location with the cells populated with the same data on sheets 4, 6 and 9.

View 9 Replies View Related

Sum Matching Rows From Multiple Sheets

Jun 14, 2008

I'm working with 2 sheets. Both have the same headings, A1 is CustomerCode, C1 is JAN, D1 is FEB and so on. The CustomerCode column doesn't match exactly between the 2 sheets, because some of the customers from Sheet1 aren't listed on Sheet2. I need a formula to put on Sheet 3 that will Sum the values in JAN, FEB, etc from the first 2 sheets for matching customers, and also bring over the totals from the customers on Sheet 1 that don't have a match on Sheet 2.

I have attached an example of how the finished product would look in my case. But also, here's this:

Sheet1 Charges

CustomerCode JAN FEB
ABC 30 25
AEF 20 15
BBB 50 30
CED 15 20

Sheet2 Misc Charges

CustomerCode JANFEB
ABC 510
BBB 3 8
CED 2 4

Need Sheet3 To look Like this:

Sheet3 Total Charges

CustomerCode JANFEB
ABC 3535
AEF 2015
BBB 5338
CED 1724

New customers are added often, so I have a SQL Query that I can put in column A of sheet3 that will pull the same CustomerCode list as sheet1, so getting the names to sheet3 shouldn't be an issue.

View 3 Replies View Related

Macro To Delete Rows Containing Multiple Criteria?

Feb 23, 2011

how they can be used to delete rows that meet multiple criteria, however all the examples I have found seem to be rather complex and personal to the individuals as opposed to being rather generic. Many people, including myself would find it useful to have a simplified generic answer that can be applied to all kinds of criteria. Such as if the data in column B = x and the data in column C = y delete the row. Here is my example.

SCENARIO EXAMPLE:

In column A - Date e.g. 05/02/2011, 06/02/2011
In column B - Day
In column C - Time (in format 09:06:21)
In column D – Service numbers dialled (70, 110, 224) two to three digits long

MACRO EXAMPLE:

Delete the row if the criteria in:
Column D = 10, 40, 192 or 244
And
Column C is between 09:00:00 and 16:00:00

macro code or at least a link to a thread that they think gives a scenario as simple as this.

View 5 Replies View Related

How To Delete Multiple Rows But That Match In Columns

May 22, 2014

ertret.jpg

I am trying to delete rows that contain the same addresses BASED ON COLUMNS.

Do you see how in columns C and D they all match EXCEPT for row 4? How do I delete allllll the rows that when c and d match.

I want to keep the ones that DO NOT MATCH.

P.S I HAVE 4000 ROWS I WOULD HAVE TO GO THROUGH

View 1 Replies View Related

Delete Rows Multiple Criteria W/Numbers

Dec 23, 2009

I am trying to use the code below to eliminate data in a spreadsheet based on certain criteria. I have been able to get this to work with text fields just fine, but now I am running into an issue with numbers. I am trying to get this code to delete rows if they have the word "unknown" in column C and if the value in column B is greater than 900 hours (999:00:00), the numbers in this column are formatted as [hhh]:mm:ss. What should I tweak to make this work?

View 7 Replies View Related

Delete And Sum Duplicate Rows With Multiple Condition

Oct 31, 2012

Here is the problem: i have two conditions to satisfy first is data from column A and second is data from column C, what i wanted to do is if both data from column A and B has the same other data in column A and C then sum the total in column D, F, I retain data for column B, E, G and H. Then delete the duplicate rows. By the way we also need to replace the remove the text (W1,W2,W3,W4 and W5) in column C.

View 6 Replies View Related







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