I tried to write a macro for deleting some columns automatically in my workbook
- it will be applied to all worksheets, except two worksheets called "samsung" and "toshiba"
- columns will be deleted from V to AA and AU to AZ in the related worksheets
Please check my attached file and my macro inside it.
deleting columns makro.xlsm
I am trying to write a macro that will look through all the worksheets in my workbook and delete any that are called "Data2", "Data3",... "Data#"
So basically I want to find any sheet that has Data and a number after it (with the exception of "Data1") and delete it. Does anyone know of any way to do this?
Would anyone have a way to delete worksheets not between worksheets called Beg and End? I am actually looking for a way to delete all worksheets to the left of Beg (including Beg) and all worksheets to the right of End (including End).
View 6 Replies View RelatedI am trying to write a macro that takes the value in cell B2 from a workbook called ""numbers" in a worksheet called "summary," and creates a new worksheet in a workbook called "filestransfer" that is named the same thing as the value in cell B2 from "numbers." That part is done.
View 2 Replies View RelatedMy spreadsheet involved plenty of importing of data for Analysis. However the spreadsheet get cluttered very often and i have manually delete the data worksheets.
I was wondering whether is there a way of deleting all the worksheets that contains data(I.E someway of protecting my proccessing and display sheets from being deleted, about 100 of them) or isit possible to delete spreadsheet in a loop with the last char being a number as all my data sheets are named xxx1 ,xyz2 etc and none of my other sheets end with a number
I'm on a system using Windows XP with several user accounts. It's possible to share a document by putting it in a directory called, unsurprisingly, "Shared files' or somesuch. Anyway, I wish to put one of my Excel spreadsheets into this directory so that other users can see it. The workbook itself has about 10 worksheets and I want other people to be able to edit only one of them. So I used the "Protect Worksheet" option within Excel to protect those worksheets I don't want edited. However, I notice that it's still possible to delete a worksheet even though its protected. Is there a way to prevent from deleting worksheets?
View 2 Replies View RelatedI need to delete line if 2 conditions are meet.
If col A is blank and col D or E is blank then I want the line deleted for lines 1no more than 100 The option of D or E has me stymied I end up with it not working right. This is what I had tried
Dim lastrow As Long, c As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For c = 1 To lastrow
If Range("a" & c).Value " " and Range("d" & c). Value " " or Range("e" & c)Then ActiveSheet.HPageBreaks.delete
Next c
But doesn't work
I also need macro to run multiple worksheets which varies anywhere from 12 sheets to 50 sheets.
Sub DeleteSheetsPlease()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Application.StatusBar = "checking " & ws.Name
[code] .......
This skips "Parameters" and "About", then it deletes a single sheet (that does not match "Parameters" or "About"),
BUT THEN IT STOPS, leaving "deleting [WSNAME]" in the appstatus. It's like it skips the "Next ws" statement altogether after deleting a sheet.
I want it to keep looping on all the sheets, but that delete action seems to kill the looping...
I am trying to find a easy way to delete multiple worksheets in a workbook and then saving the workbook based on the tab name of the worksheet.
View 9 Replies View Relatedwhenever you want to delete a sheet, excel would prompt you to ask you if you are sure you want to delete the sheet. im making a vba so that the sheets will delete upon workbook close, but i dont want to be prompted everytime to be sure to delete it when running the vba macro. Also, i would like to avoid being asked to save any changes to my workbook
View 2 Replies View RelatedI have many worksheets which ends with the word " data". Is there a way to delete all the data sheets at one go through VBA code.
View 3 Replies View RelatedDeleting Columns. I've got 2 codes here,
This:
I have an excel spreadsheet with Extra columns that have no data in them. I want to delete this columns so that there is no horizontal scroll, but highlighting and right-clicking and choosing delete isn't working. Not sure why. How can I delete?
View 2 Replies View RelatedI want to delete columns that contain a 0 value in row 2 of the column.
In my case, I have headers in row 1 and values in rows 2 and below. I eventually want to filter out the columns that contain no values below the headers.
Let me know if you need any more information for clarification.
I am trying to build a macro to delete the cell content for the entire A column, B column, C column. Then i would like the macro to delete the cell content from row 3 to the bottom of the sheet.
View 9 Replies View RelatedI have a workbook that has worksheeets for every day of the month. The data in the worksheet consists of columns (employee #, name, clock in/out times, and break penalty).
What I am trying to do is create another worksheet that searches all the other worksheets for a "yes" in the "break penalty" column and then create a list of all the employees that received a break penalty for the entire month. I would like this to be able to auto populate throughout the month as data is entered and not have to use a filter every time I want to compile this list.
I'm sure this is simple code, but...
I have a data table with over 20,000 rows that refreshes each day. I need to run a bit of code when it refreshes that says if the value in column R is 2, then delete that row.
I need to delete rows that are blank, 0, or contain errors (#REF, #N/A, etc). My problem is that once in a while, an error will show up in a singe cell of a row that I need to keep so a simple deletion of only errors/blanks/0 will not work for me.
The two cells I need to check for errors, blanks, and 0's are D and E. There can be an #N/A in col D and a 0 in col E or both D and E can have errors. Blanks will occur in all cells of the row.
I have tried to use the code below but it doesn't do anything, but it also doesn't give me an error.
Code:
Sub DeleteAll()
Windows("Template.xls").Activate
Dim r As Long
For r = 2 To 36500
If Range("D" & r).Value = "#N/A" And Range("E" & r).Value = "#N/A" Then
Range("D" & r).EntireRow.Delete = True
[code].....
I am having trouble coming up with an algorithm for deleting columns, based on a certain set of criteria. Heres the issue, all the columns have a "1" the top of column . If that column has a cell number that's greater than or equal to 0.90 or less than or equal to -0.90 then that column gets deleted, if it just has a "1" then the row doesn't get deleted. Very complicated set of criteria.
View 6 Replies View RelatedI'm building a bookkeeping workbook that is only currently 800kb with TWO cells that have data validation and no cells have conditional formatting. There are a fair few formulae but they are all simply SUM, SUMIF and CONCATENATE.
View 1 Replies View RelatedI found a useful resource on the web that gives a macro that deletes rows when the cells within a SINGLE column are identical. [url]
Does anyone know of a macro that can do more than that, one that will delete rows if ALL cells within ALL columns are identical?
For example, the macro should delete row 4 of the attached spreadsheet. It should leave row #2 there because it's the orginal row. But the duplicate row #4 should be deleted. The macro should leave row #5 there because not all columns are identical for that row.
I have a spreadsheet with 20 or so columns but that number can change at any time. I need my code to by dynamic so if a column is deleted (or added) my code will still work.
Each column has a name in row 2 and I'm trying to use that in order to make everything dynamic. For example, my code counts the number of blue cells in colums D4:G68, but now if someone adds a column before column D, everything gets messed up and it will still count the cells in D4:G68 but I want to use E4:H64 now. Does that make sense? I have made code that searches the column names and returns the column number to correspond to the name. How would I change this?
Range ("C8") = "= CountBlue(E4:G68)"
the code doesn't look exactly like this, I believe it is actually in R1C1 format
I am trying to write a VBA code in excel to delete any columns when a cell in that particular column = 0 (zero). That cell would start at B19 and subsequently follow to C19, D19 and so on.
View 9 Replies View Relatedlets say i have 200 used columns. if i delete 150 of them and then (without saving the file) try to add 100 more i get an excel has reached its limit error. theoretically 200-150+100 = 150 columns, which should be well within the 258 column limit. but excel still gives me the error )its like its calculating 200+100=300 > 258). HOWEVER, if i save the file after deleting, i dont get any errors at all. is this a "feature" of excel and if so is there anyway of circumventing it? because i dont want to save the file prior to adding the columns (i do the whole thing in vba). actually i dont want to save the file at all unless the user click on the save button or choose save/save as from the menu.
View 9 Replies View RelatedI'm trying to write a macro that will look at a table of data, and if column J is blank, it will delete the entire row and shift the data up. Also, if there is a "C" in column C, it will delete that entire row as well. I tried the code below, but for some reason it is not deleting all of the rows that it should. Is there some kind of problem with the code that I'm not catching? It looks like it should work perfectly...
View 9 Replies View RelatedMy data in excel looks like this:
A B C D
Visit_Date Day_of_the_VisitHousing_Tour_TimeNumber_in_Party
3/22/2012 0:00Thursday 12:00 P.M. 2
3/22/2012 0:00Thursday 12:00 P.M. 4
3/22/2012 0:00Thursday 12:00 P.M. 1
3/22/2012 0:00Thursday Not Requested 2
3/22/2012 0:00Thursday 12:00 P.M. 3
3/22/2012 0:00Thursday 12:00 P.M. 3
3/22/2012 0:00Thursday 12:00 P.M. 4
3/22/2012 0:00Thursday Not Requested 1
3/22/2012 0:00Thursday 12:00 P.M. 2
3/23/2012 0:00Friday Not Requested 2
3/23/2012 0:00Friday Not Requested 5
What I'd like to be able to do is if Column A,B, and C are the same to sum column D. So, for example, I would like the data above to look like this:
A B C D
Visit_Date Day_of_the_VisitHousing_Tour_TimeNumber_in_Party
3/22/2012 0:00Thursday 12:00 P.M. 7
3/22/2012 0:00Thursday Not Requested 3
3/22/2012 0:00Thursday 12:00 P.M. 12
3/23/2012 0:00Friday Not Requested 7
I work with a spreadsheet every week to input values and subtotal them. These values change constantly and instead of going through and manually deleting each row in a 100+ row spreadsheet to be able to import into another program, I'm looking for an quicker way to keep my data in order but consolidate by getting rid of only the rows where both column A and B are blank.
Here's an example of what I'm working with:
5
10
15
20
50
2
4
6
8
20
This is what I need the final product to look like:
5
10
15
20
50
2
4
6
8
20
So I would like to quickly delete rows 2, 5, and 11. All of the other answers I've found only show how to delete rows based on empty cells in only 1 column. How can I quickly delete the rows where both columns are empty?
I'm trying to come up with a macro that can delete an entire column based on formatting of the cells. Basically what I have is a range of cells, and if all cells in a particular column have no fill color, then delete the entire column.
View 9 Replies View RelatedI have 2 worksheets in which I have to copy one column of cells from one to
the other. The problem is that the "main" worksheet lists nearly 3,500
clients and I have to copy numbers into the "main" worksheet on weekly basis
from another worksheet that lists only about 1,100 of those 3,500 clients.
Both client lists are in alphabetical order, but I obviously cannot just copy
one column into the other, because then the numbers won't be matching the
correct clients.
Will excel allow text to be permanently be positioned in a specific cell even if rows or columns are added?
View 6 Replies View Related