Delete Columns From All Sheets Based On Condition In X Workbooks
Mar 28, 2008
I am sure this is a very simple questions. I am using the below code to work on the worksheets on a workbook called "MF BANK EXPOSURE SUMMARY.xls" so far the code that I am using is:
Sub Commandbutton2()
Dim iCol As Long
Dim Isheet As Long
Dim Item As Worksheet
Set MFBANK = Workbooks("MF BANK EXPOSURE SUMMARY.xls")
For Each Item In MFBANK.Worksheets
With Item. Range("A1:T65536")
For iCol = .Column + .Columns.Count - 1 To 1 Step -1
If IsEmpty(.Cells(65536, iCol)) And IsEmpty(.Cells(1, iCol)) Then
If .Cells(65536, iCol).End(xlUp).Row = 1 Then .Columns(iCol).Delete
End If
Next iCol
End With
Next Item
End Sub
I would like to extend the code so that it works on the worksheets of two workbooks at the same time the one being the "MF BANK EXPOSURE SUMMARY.xls" and the other being "MF CP EXPOSURE SUMMARY.xls" .
I want to delete a row in excel based on a condition.
The condition is as follows:
I have few rows in the excel where the columns 7,12,13,23 are same and column 24 in one row has some number and in some other row the column 24 is blank with orange color filled(the colorindex for orange is 44).
In this case I want the row with orange color filled in column 24 to be deleted.
i have an issue i need to delete entire row if a condition is met, i need syntax if possible to delete if a cell word "begins with" a certain letter ...in this case...letter A
Is it posibile to make a macro that automaticaly delete rows based ona a condition. Condition is to delete rows that have B column value "" or zero. For example:....
I want to be able to delete certain rows based on multiple criteria. If data in the row does not meat the crieteira, it shud be deleted. I have attached the excel file sample in which I want to keep the highlighed rows and detele the rest. Its base on Origin city and Destination city combination. Eg: If Origin city="A" and Destination City = "B", I want to keep the row. Like wise for all highlighted rows. but if the Origin City = "B" and Destination City = "U" I want to delete the row.
i have a set of data as below and wish to delete ENTIRE ROW if cell is the same but keep the first entry when code comes to it..ie in the data set below excel would keep first row and delete 2nd,3rd,5th, 6,7,8 and then go to next unique identifier which would be AU0000LIFHB3 this would not be deleted as it is unique the would proceed to AU300GPTC011. this process would then stop when no data was available
Sub DeleteBlankRowInColumnA() 'this macro scrolls down Column A if the cell is empty the row is deleted. Dim A As Integer A = 0 Do Until A = 142 If Range(0, "a1") = 0 Then Selection.Delete shift:=xlUp End If Loop
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
I have a worksheet with part numbers, and I want to delete all the rows with part numbers matching a part number in another workbook (I could import the list into the part number workbook in a new sheet if it makes it easier). The part numbers in the other worksheet would be in column A.
I would like to Use my Excel VBA program to search each row in a csv document for a name (located in a cell ) if the name exists then I would like to delete the entire row.
Whenever I try to do the above in excel, even when I save in csv format the file formating or something gets changed and the file which has to be procesed through another program then process incorectly.
Is there a way to do what I need while retaining the formating which must obviously be changed when the file is opened in excel?
Having book1 and book2 (that is closed). Is there a way that if I erase a value "X" in book 1, update book2 (without open it) deleting the complete row/rows where the value X is found?
this may or may not be easy for some of you but its driving me nuts. Here's what I need to do.
1.Create a new workbook
2.Cycle through multiple workbooks and all worksheets within each workbook (all in the same folder). All workbooks will have the same structure.
3.Take the worksheet names and put them in rows
4.Take the data from the first column (column A) from the first worksheet of the first workbook and put them across the top (i.e. transpose the data). The first column will be the same in all workbooks so it doesn't matter where I get it from.
5.Copy the data from column E from each worksheet and paste them in rows in the new workbook (again transpose) corresponding to each worksheet.
6. Perform simple mathematical calculations at the end of each row.
I expect to have approximately 26 workbooks with a total of 7000 worksheets. In the target workbook (i.e. the new one), I expect there to be 7000 rows (corresponding to the 7000 worksheets) and about 260 columns.
The only reason I need to transpose stuff is because Excel 2007 doesn't have 7000 columns.
Here's an example of what it would look like. Example worksheet (Input)- Worksheet ABC Col A Col E 1/1/2004 $25 1/8/2004 $30 1/15/2004 $15
Imagine another worksheet called LMN with the same ColA but different values in Col E.
Output workbook ColA Columns B Column C Column D Sheet 1/1/2004 1/8/ 2004 1/15/2004 ABC $25 $30 $15 LMN $xxx $yyy $zzz
I have a single registration sheet that lists names, contact information, and the class someone is in on a single sheet.
(Name, Number, Email, Class 1, Class 2)
I am hoping to automatically populate other sheets in my work book based on classes... essentially making automatically populated rosters. For example, everyone who has Math listed in either Class 1 or Class 2 would have their entire row (with contact information, etc) copied to the "Math" sheet.
I want by using some code I've seen on this forum or using the macro writer and then tweaking the code. So with that said, I've written the attached code but I know there is probably an easier way to write it. It cycles through about 12 sheets using the same below code, but I didn't list that code.
Sub Hide_Rows() Dim i As Integer For i = 3 To 418 Sheets("AFA - UMBI").Select If ActiveSheet. Range("b" & i).Value = "2008-2" Then Rows(i & ":" & i).EntireRow.Hidden = True ElseIf ActiveSheet.Range("b" & i).Value = "2008-3" Then Rows(i & ":" & i).EntireRow.Hidden = True ElseIf ActiveSheet.Range("b" & i).Value = "2008-4" Then Rows(i & ":" & i).EntireRow.Hidden = True ElseIf ActiveSheet.Range("b" & i).Value = "2009-1" Then Rows(i & ":" & i).EntireRow.Hidden = True ElseIf ActiveSheet.Range("b" & i).Value = "2009-2" Then Rows(i & ":" & i).EntireRow.Hidden = True ElseIf ActiveSheet.Range("b" & i).Value = "2009-3" Then Rows(i & ":" & i).EntireRow.Hidden = True ElseIf ActiveSheet.Range("b" & i).Value = "2009-4" Then Rows(i & ":" & i).EntireRow.Hidden = True End If Next i End Sub
I have three columns - as listed below. I need a script - That compares "result 1" and result 2. If there are data in the "result 1" column then input that into the "City" column. Else input "result 2" data (if any exist), else leave blank.
"Result 1" always has priority over over "Result 2".
15.png Attached is the file & snap shot for the problem.
TABLE A [table="width: 500, class: grid"] [tr] [td]Catagories state names devices A4-100HP A ALT
[Code] .....
TABLE B Catagoriesstate names devices A4-100HP A4-101MP A4-102AP
[Code] ....
In above condition we want to have an automatic filling up of data in table B in column (name & device) but it should match first with state name with category codes of table B & then same with table A in order to avoid any wrong entry because in table A certain states are repeated but codes are unique so we want that filter should match two column of each table before filling data in table B.
I have been trying the whole day to create a formula to solve this but I just can't get it. To make it simple, let's say I have column A filled with different people names, column B filled with Level of Complexity and column C with the Activity (work task) title. This table is fed dynamically, so the number of rows can vary from 2 to 2000.
What I want to do is count how many times a single name appears related to the same complexity, so I can tell how many "simple, medium or complex" tasks each person has in their work queue.
A simple table example would be: colA colB colC Dan Complex Build House Dan Complex Build Shopping center Dan Simple Take dog to walk Jose Simple Clean desk Maria Medium Paint office roof
Then, on another cell I'd like to have a formula automatically telling me that Dan has 2 Complex tasks and 1 Simple, and so on - considering number rows is not a fixed number. What I have so far is this: =SUMPRODUCT((Data!$I$2:$I$457="Dan")*(Data!$L$2:$L$457="Complex")) But I need to tell a specific last row, and if the data worksheet gets updated with less data (say 450 rows) the formula breaks...
Lets say we have data in Column A, B, C and D and no row left blank. In the column A no cell left blank however in B, C, D any one cell only contain a value in that row. If B10 has any value in it then C10 and D10 are left blank (not empty). I want to segregate the data in Column A based on the value in B, C or D. So this one column data ( that is Column A) will split into three column. this segregated data to be put in E, F and G.
Wherever Column B has any value that's greater than zero content from the column A from the same row should copy to the E, Wherever Column C has any value that's greater than zero content from the column A from the same row should copy to the F, Wherever Column C has any value that's greater than zero content from the column A from the same row should copy to the G.
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.
I'm trying to delete sheets based on a list in Excel 2003 using VB, any ideas how to accomplish it as the various ways I've tried haven't even come close to working!
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.
I have a situation where i would like to hide and delete a few sheets in my worksheet.
there is a few sheets where i want it to be constantly visible and one sheet is based on a cell reference.
the remaining irrelevant sheets i would like it to be hidden and deleted.
'always visible are sheets that are constantly visible in all files' 'visible msds is the sheet where my product data is being pulled out based on a input i enter on my first sheet' the sheets i want to hide and delete will be the remaining irrelevant "MSDS" that i would like to remove through macro.
1) 4 values contains in each row based on the values from those cells the max value will display.
2) if more than 2 cells have empty,NR or NA text means the entire row has to delete.
3) if 2 or more that means 3 cells having values the empty cell,NR or NA cell will place value with the condition of macro that is 75% of other values which is maximum among them.
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.................
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
for sheet name Par1 , I want to keep first three columns and column name with Par1...same for all the sheets. But there are some exception.
For sheet name Par4 , I want to keep first three columns and column name Par4, and Par7 .
For sheet name Par5 , I want to keep first three columns and column name Par5, and Par7 .
For sheet name Par6 , I want to keep first three columns and column name Par6, and Par8 .
I am using the below code for the same. But this is not working.
Code:
mybook.Activate For Each sht In mybook.Sheets sht.Activate For j = sht.Range("A1").CurrentRegion.Columns.Count To 4 Step -1 If sht.Name "Par4" Or "Par5" Or "Par6" Then If Cells(1, j) sht.Name Then
I have a spreadsheet that in column A has either the letters "C", "LP", or is blank.
In Column L it has either numbers 1 through 100, or the letter "D".
What I need it to do is to check and see if the data in column L is "D" and in the same row column A is blank, then delete that row.
Also, if the data in Column L is "D" and the Data in column A is either "C", or "LP", then I need it to change the "D" to "1".
It might be easier to do it separately because once you run the part that deletes the rows that dont have a C or LP then I could probably just do one that says if column L = D then change to 1.
Lets see if I can simplify this for you.
If Column L = D and Column A = BLANK then Delete Row (this would delete all Ds that dont have the C or LP)
First problem: in column B, I have a formula based on contents of column A. However, the limitations of 7 nested IFs leaves me with errors - I need 3 more IFs. Is there a way around that?
Second problem: I need to show the row of the first (earliest time in column A) and the last (latest hour in column A) record for each name. I've been sorting by name and time, then manually deleteing the rows I don't need. I know there's a better way, but I can't find it!
