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]....
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).
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.
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 RelatedI 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.................
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..
It is supposed to take the rows with striketrhoughs and transfer them to the other sheet.
Private Sub Worksheet_Activate()
If Cells.Font.Strikethrough = True Then
Rows.Select
Selection.Cut
Sheets("Completed Deployments").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End If
End Sub
I need create a macro to perform the following - I don't know if it needs to be an Excel macro or via VB. This is to speed a process up and eliminate any spreadsheet messing around for our "admin" staff - they are not good with excel at all!
So, I have a daily/weekly down load of a spreadsheet (exported from access).
* I want to copy the values of column AK to A
* Then delete all entries in column D that equal 19, 20 and 6
* Then delete all entries in column AL that equal "A","B","C" or "D" but not "E","F" or "G"
* Then delete entire rows where the value in column A is a duplicate within the list
I will call this spreadsheet, for the purpose of this exercise, the "Master". I now have another spreadsheet with values in that should relate to values in either (or both) columns A and B in the "Master". I need Spreadsheet 2 values to be highlighted as cross checked in the "Master" and then all those in the "Master" that do not appear in the 2nd S/S will be normal white cells thus showing that they need to be chased by our Admin staff.
I'm trying to sum up a column of cells which have both text with and without strikethrough formatting. I've looked around and got some VB codes but it doesn't work for me.
I still get the annoying #NAME? error. It did work initially but after moving the file to another computer it just became #NAME? again.
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.
I would like a macro to find the columns named "apple" and "peach" and delete them. These would always be in row 1 but would always be in different column letters which is why I want the macro to simply find these columns by their name and not by their column letter.
And yes, I do mean the entire column altogether, shifting entire columns to the left. Wipe it off the face of the earth
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.
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
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.
Below is my code.
It opens a file, does nifty offset formula to compact rows A and B to rows E and F. The problem, is once it is done it leaves a trail of 0's in cells that had no data in the original file.
I have been trying to figure a way to just delete the rows in column A that have a zero in each worksheet (there are 7)
Code:
Option Explicit Private Sub CommandButton1_Click() Dim fromwb As Workbook Dim ws As Worksheet Application.ScreenUpdating = False ' Opens file to select With Application.FileDialog(msoFileDialogOpen) .Filters.Clear .Filters.Add "Excel 2007",
[Code].....
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
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.
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 RelatedI 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
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).
I am trying to move through the worksheets and delete all columns with "Accession" in contained in them. I have to do this with an external macro as the spreadsheet with the data is created from another program. I tried this and get an 'Object or With block variable not set' error and the debug highlights the Cells. Find line of the code.
Also, the number of columns could be variable within the spreadsheet as it is compiled by the other program.
Sub DelAccessionNum()
Dim Wrkst As Worksheet
For Each Wrkst In ActiveWorkbook.Worksheets
Cells.Find(What:="Accession", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
, SearchFormat:=False).Activate
On Error Goto Completed:
Selection.EntireColumn.Delete Shift:=xlToLeft
Completed:
Next
End Sub
I have a sheet where i want to delete duplicate rows where column A and column B combined are equal, i.e. range(Ax:Bx) where x is the current row. I am using the macro below but cant seem to get it working as I keep getting a type mismatch error and Im not sure why.
View 2 Replies View RelatedData 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.
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.
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.
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 RelatedI'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.
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 .............................
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 RelatedI 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