Code:
Range("A1,A:A,C:C,E:E,D:D,G:G,H:H,I:I,J:J").Select
Selection.Delete Shift:=xlToLeft
When I wrte a macro as above, i get error "the command cannot be used with selections that contain rows or columns and also other cells. Try selecting only entire rows, columns or just group of cells".
Actually i want delete all columns except B:B and F:F
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 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
In a sheet where the longest column is A, I want to delete the rows between the last value in Column A and the last value in Column C.
I'm trying to create a short script which performs the following:
Searches for a specific name in column C on my "Resource List" sheet, then When this is found, check column H to see if this is populated,Then when both criteria are met delete the entire row.
Using a link I found here [URL], I've put together the code below:
[Code] ....
The problem I have is that although the criteria are being met the row is not being deleted.
Macro below would be to only go down to the cell = to the bottom-most cell with data in column A.
These columns below are all in row A (the title row)
If column named "paper" says "white" OR "grey" AND
column named "pizza" says "mushroom" AND
column named "size" says "large" AND
column named "flavor" says "butter pecan"..
(again, only going down to the same matching cell with any data in column a)
Then delete these columns.
Using the following code to remove empty rows based on whether a specific range of columns is empty. The code works if the cell has a zero, but not when the cell is blank. An example of the data is attached.
VB:
Public Sub DelRows2()
Dim Cel As Range, searchStr, FirstCell As String
Dim searchRange As Range, DeleteRange As Range
[Code].....
In .Range("A8:CJ8") I want code to delete all columns within that Range that do not have any values in the cells.
View 9 Replies View Relatedertret.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
I have a file with many intercompany transactions. There is a range of business unit numbers that if anyone one number is in both Column B "Bus Unit" and Column E "Affiliate" I want the row deleted.
Please see the attached, it is more clear I hope. The two yellow rows would be deleted.
The list of business units that I would want to never be on the same row is "4, 7, 41, 44, 46, 51"
I am looking for an Excel VBA code that can delete pair of rows that has certain criteria. I have included an example in the attachment and highlighted the rows that should be deleted. Since the values in each coloumn is going to change every day , I am looking for a dynamic code.
The objective is to:
To delete rows that has same code (columnd D), same basis (coloumn E), same Effective (coloumn H), value of TT (Column J) is either TI or TO and has offsetting Amounts (L) ie the sume becomes zero. Example pair of row 7 and row 8 as well as pair of row 12 and row 13 should be deleted.
TO and TI are actually transfer in and transfer out. Since the TI and TO for these rows make the amount zero for same code and same basis on the same effective date, I do not want to include this in the spreadsheet.
I have written a complex macro but i would like to delete some of the columns from my spreadsheet using the macro? I can highlight the column but then dont know what next to write to get the column deleted.
View 4 Replies View RelatedI am working with Excel 2003. I have a series of spreadsheets generated by my companies database program. For a special project I am trying to work on, I would be able to get my results much much quicker if I could get a macro that would do the following:
The first row is a list of headers. I need the macro to look at each cell below the header, and if any data exists in that column, I need it to delete the entire column. The results I will get will tell me quickly which headers are in certain jobs but are constantly not being used. They are trimming the database at work and this type of macro would help me tremendously.
For now, I have data in Column A to E. I have the first row (A1 to E1) contaning different numbers. I need a macro that delete all collumns that cells (in A1 to E1) contain number 1. Next month, I may have 10 columns (A to J) and I want to run the same macro that delete all columns that A1-J1 containing value of 1. The number of columns changes every month, so I want a macro that work to the last column without the need to specifying that column range.
I was using this, but did not work propery (since It just deletet every other column & have to run it a few times to complete & need to specify column range):
Range(A1:E1).Select For each cell in selection If cell.Value>1 Then
Cell.EntireColumn.Delete End If Next cell
I have a CSV file with the following columns:
column1, Column2, Column3
A, B, "C,D"
I need to write a macro to :
1. Delete column2
2. Save the CSV file. At the time of saving I need to supress all default messages.
I am developing some code to extract selected worksheets to a new workbook, and provide the data in a "standalone" form where certain formulas have been converted to values etc. As part of this project, hidden columns are made visible, then data is pasted as values on a row by row basis, then the previously hidden columns are deleted. Here is the code that unhides the columns and identifies those columns as ones that it needs to delete later on:
'Expose hidden columns and keep a list
For i = 1 To 200
Range("a1").Offset(0, i - 1).Select
If ActiveCell.EntireColumn.Hidden = True Then
colnum = ActiveCell.Column
ActiveCell.EntireColumn.Hidden = False
If HideCOL <> "" Then
HideCOL = HideCOL & "," & colnum
Else
HideCOL = colnum
End If
End If
Next
The macro then goes about its business doing this and that, and at the end comes back to delete those offending columns whose numbers have been stored in the variable "HideCOL".
'Delete columns that were hidden
If HideCOL <> "" Then
Dim colArray As Variant .............
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
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 RelatedI'm running a macro that opens another workbook and read data from it.How can I incorporate this code into my macro.Sorry i don't knwo VBA.
Workbooks.Open Filename:="C:Documents and SettingsmsimantbDesktopINFRACHEM_POLYMERS - DON''T DELETE.xls]Sheet1"
UserGRP_MAcro Macro
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:E").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").EntireColumn.AutoFit
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("B1").Select
ActiveCell.FormulaR1C1 = "Existing userGroup"............................
I am looking for a way to close a group on Workbooks that are in separate instances of Excel.
These Workbooks get opened similar to running a Powerpoint program, but much different usage than Powerpoint.
AS AN EXAMPLE:
I have 7 Workbooks open in Excel . . . Could be more or less.
Each workbook has 2 sheets, the "User Sheet" (not the actual name, as all user sheet names vary) and a sheet named "Setup." (Always named "Setup.")
On the "Setup" sheet I have a list of all the possible Workbooks that I want to close. For reference purposes only, I'll call this list "To Close". All 7 Workbooks would be listed, but not all of them would be opened. If any particular Workbook on the "To Close" list is not open, it would simply be bypassed.
Example of list: Column A
Row 25 has the name of Workbook A
Row 26 has the name of Workbook B
Row 27 has the name of Workbook C
And this would continue down to the last Workbook possibly open. I will know how many Workbooks could possibly be open.
Other workbooks could also be open, but they would not be closed by this macro. In this example, I would only want Workbooks A & B & C closed.
I would have rows 25 thru 27 (or more) listed in the same location on the "Setup" sheet of each Workbook.
Each "User Sheet" would have a macro called "Close Group"
The macro would have to search through all open Workbooks and when it found one of the Workbooks listed for closing, it would close that workbook. Then, the macro would look for the next Workbook on the "To Close" list.
The Active Workbook would also be on the list and that also needs to be closed. I would guess that this would be the last Workbook to close, but it really would not make any difference if the macro would still work. I think the macro has to have the active Workbook open to close the others.
After the list has been gone through, the macro would stop.
NOTE: Each Workbook has it's own "To Close" list. If it would help, I can leave the Workbook Name A off of the list in Workbook A. I would do the same on Workbook B, etc. Then the Active Workbook could automatically be closed at the end of the macro.
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.................
I have a large workseet that I want to Delete all rows where value in column Q is > 9.99 and < 60.00. So the only rows left would be where column Q value is < 9.99 and > 59.99. (deleting out the middle amounts) I think this might be a nested If stmt but have not figured it out so I'm not sure if there is another way.
View 4 Replies View Relatedhow 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.
I have a table of data with which I need a macro to clear a range of data based on certain criteria. I have attached a file as an example. In this example the current period is 6. I therefore need to clear columns which have a period of 7 or higher.
Essentially, if the current period is x, then clear the range in the period columns if the period is greater than x.
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.
I currently had to start from scratch as my formula kept raising the debug screen. I know the answer is simple but I cant figure out what it is. I have column A, rows 9 through 1000 with data. I want to delete the entire row of any "x" value present in column A rows 9 through 1000. I am new to VBA. So simple is good.
View 2 Replies View RelatedI am trying to create a macro that would be able to delete an entire row of data if a value in column A is equal to a range on a different sheet.
On Sheet “ISQ Raw Data”
If any value in column A (starting in row 2) is equal to a value on Sheet “Old Response IDs” Range A:A(all of column A)
Then Delete that entire row in Sheet “ISQ Raw Data”
I have written VBA code which is operated every month. This code deletes several range names in a target spreadsheet, performs other actions - including extracting certain areas to different spreadsheets and then closes the spreadsheet without saving.
The range names in the target spreadsheet are required for ongoing use, so can not be deleted permanently.
Is there any code that deletes all range names in one go? At present my code includes the results of recording a macro wherein I delete each range name in turn - creating script over 100 lines long.
A macro is required to identify rows within a selection e.g. entire column A, that share the same value, then delete appropriate rows depending on the values in another column. The attached example details the requirements.
Extract Rec1.xlsx
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