I have Work book that is quite big and I was silly and put in tables, but this ultimately slows down excel so much so that I have to wait 20-30secs while it performs a single task (PC is i7 with 4g of ram).
B/c I have been working on this Workbook for a long time it has multible sheets all interconnected and I can't go through all the sheets and redo the equations , as that wouyld just take too much time. Is there a way of leaving the data the way it is and deleting the table? This is an example below of the type of table I am talking about.
I have 3 columns of data that are being copied from a .csv file. The data is pasted into Columns B, C, & D. Columns C and D are hidden. So that the user does not have to unhide the columns in order to delete the data, I would like to make it so that when the data in column B is deleted, the data in columns C and D is also deleted.
I have an month input in cells B2 (user can enter values from 1-5)
I then have a data table that has month 1,2,3,4,5 running across range G9:K9. the data is held in range G10:K19.
So if user types in 4 in B2, what should happen is that months 1,2,3 and 4 the data for these months should be cleared and data in month 5 moves forward into month 1.
So basically, anything left of the month entered is B2 should be cleared and replaced with anything right of the month in B2.
i have a large amount of data with a lot of formatting (font sizes, colours, fill colours, mulltiple conditional formatting etc) and i now want to turn it into a table so i can use a data filter without mixing it up with the data that follows.
i tried leaving a blank line after it because i thought the filter only worked on a continous block of populated cells but it still includes rows after it so i'm going to define it as a table.
however, i spent ages getting the formatting right and from what i can see the action of creating a table applies new formatting. Is there a way to create the table preserving my formatting?
if not, the only workaround i can think of is to make a temporary copy of the range and use that to copy and paste the formatting back to the table. would that also work?
Currently, Im running a button macro. When this button is clicked, the table from "315 Employee Data" will copy the names from column C to "315" sheet B12 onwards.
Now, what i want is when I update the employee data on "315 employee data" sheet, I want "315" sheet to automatically match the names from "315 employee data", delete and add names when I add or remove employees from the column the next time i click on that button again.
After the above is achieved, when i add new employees,run the macro and it displays the updated list of names, I want "315" sheet column A to do automatic numbering.
I have attached the file for your reference.samplesample.xlsm
The below code deletes blank columns in two work books. Now I would like to add to this code by performing an additional action on the worksheets. Below is the code that I have so far:
Sub Commandbutton2() Dim iCol As Long Dim Isheet As Long Dim Item As Worksheet Dim MFBooks(2) As Workbook Dim lngIndex As Long Set MFBooks(1) = Workbooks("MF BANK EXPOSURE SUMMARY.xls") Set MFBooks(2) = Workbooks("MF CP EXPOSURE SUMMARY.xls") For lngIndex = 1 To 2 For Each Item In MFBooks(lngIndex).Worksheets .....................
I am using Excel on a work computer that someone else may have changed the settings on.
I want to simply delete a cell, without deleting the entire row the cell belongs to. Yet when I highlight the cell, and go to Edit -> Delete, the program asks me if I want to delete the entire row, and only gives me two options: OK or Cancel. So I can't delete just one cell (and have all of the lower cells shift up one).
When I right click, the 'delete' option is not present either. I tried changing the menu properties to add the 'delete' function, but it will not delete a cell without taking out the whole row.
I have a long spreadsheet with multiple dependent lists. If a change is made to the spreadsheet such as deleting a row, the validation is removed. I have to re-do the validation each time I make a change. Is there a way to "lock" the validation so that whatever changes are made to the spreadsheet, the validation stays?
I have some Job numbers that are duplicates. I can't just do a loop and delete any duplicates that come across because I have to be sure the right one is deleted.
I want the old data to be deleted if there is a duplicate..I just don't know how to verify which one is the old one using VBA. I was thinking of maybe using an advanced filter, but I don't know enough about them.
Is there code that will check both duplicates and delete the old one?
I have made an excel document that I use as a order form. I have size columns; qty columns; style number columns etc. After I enter in qtys to a sheet I than sort the sheet by the qty column which than brings all of my ordered styles to the top of the order form. I than delete everything that is below the last column that has qtys in it.
I have added pictures on the side of the worksheet which fit into the grids of each row that corridinates with the appropriate style number. I can sort the worksheet and all of the pictures stay in the appropriate columns; so far so good.
The problem that I run into is now when I go to delete all of the other columns that the customer didnt order none of the pictures delete. They jsut pile on top of each other and I cant figure out how to embed each picture to the appropriate row so that when I delete the row the picture deletes as well. At this point I have to go through and delete picture by picture and there are way to many pictures to do this.
How do I imbed the pictures into the row so that when i delete the row the picture deletes as well
I want to delete the contents of every cell without deleting any code from my module. The reason I ask is, I'm reading from a text file into excel.
I want to run my code to read text into excel one time through. Then i want to delete all the text, make some alterations to my code, and run the code again. That way each time the worksheet is fresh and clear before i run the code.
It asks me every time if I am sure and I have to click to continue. Is there any way to remove this prompt or set it to continue without my intervention?
whenever 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
Is there anyway to run this where the 120 is inserted dynamically. I tried the following but it does not work. Test Delete Row is a named range. It does insert the correct row number, but still does not execute
Dim DelRow As String DelRow = [TestDeleteRow] [DeleteTest].Select Selection.ListObject.ListRows(DelRow).Delete
I'm working through a filter macro to delete unecessary rows of data from my dataset.
- I have a Dynamic Range for my dataset called "CanadaData" - I'm trying to delete rows from the 5th column of my dataset for cells containing "DIRECTSHIP"
The macro filters the range fine, but when if comes to deleting the row, the macro stops.
Sub CanadaWarehouseFilter() x = Range("E" & Rows.Count).End(xlUp).Row If Application.WorksheetFunction.CountIf(Range("E22:E" & x), "DIRECTSHIP") > 0 Then
I am working on a sales sheet for my business. I have a worksheet that has the names of everyone in my store that has sold anything in column A. I want to create a list that has just my full time sales people and will delete everyone else.
I have two pieces of code that perform virtually identical functions on different sets of data.Basically, the code deletes rows of data based on a selection in a listbox. One code works perfect, the other has an issue.
The Issue:
When I select a single row for deletion, or multiple contiguous rows, everything works fine. It is when multiple Non-Contiguous rows are selected that the problem occurs. I get a "Delete Method of Range Class" error.
The data for the code that doesn't work is in a defined TABLE, the date for the code that does work isn't. So I assume this is where the problem lies.
I converted the data for the code that did not work from a table to a range, and everything works fine. However, I would really prefer that this data remain a table. There is consistently going to be rows added and deleted, and a table will take those adjustments into account, whereas a named range shrinks each time I delete a row, and doesn't grow as I add rows.Here is the code that works:
VB: 'Delete Selected Offers Else For r = 0 To ListBox1.ListCount - 1 [code].....
I have the following codes to delete all blank rows in column A
Dim lastrow As Long lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row MsgBox lastrow
With Sheet1 For t = 1 To lastrow If Cells(t, 1) = "" Then Rows(t).Delete End If Next t End With
End Sub
Although it is working , it is not deleting all the blank rows at once, I have to keep pressing on the macro button running the macro several times, until all blank rows are completely deleted.
I have several files of data that are from a data logger. The data is broken up by day. Each day has roughly 43000 rows of data, at its sample rate. This has made charting the data a nuisance. Is there a way to resample the data so it fits in the 32000 points excel can chart? In the future I will set a sample rate that will keep the number of points below the 32K per series. I would like to be able to have the total number of points reduced by averaging the data not by simply deleting one of every four rows.
formula to make data on same line.On converting data2 is above data 1. I insert a column on left . Need to use mouse right click shortcut delete manually shift cells left and then delete shift cells upto have both data on same line but should be in different cells and same sequence per attached ALSO TO REMOVE "/ -DASH HYPENS.
What I need is formula to move the data2 on same alignment and to delete the two empty rows between both data.
See annexed file for expected results.I have preference to have a formula much more.
I am currently dealing with an excel sheet that I am using to track people that I have contacted over the phone.
I created a drop down box for a column titled "Contacted". Under this drop down I have included: "Yes" - I reached them; "YES/CB" - I reached them, but need to call back; "NO/LVM" - I did not reach them, but I left a voicemail; and "NO/NVM" - I did not reach them and I was unable to leave a voicemail.
My goal is to have several Sheets in this 1 workbook.
Question: How do I get the WHOLE row of data to transferred to a new sheet(s) depending on the selection I make, i.e. "Yes/CB" or "NO/NVM" ??? Make note: I would like the data to disappear from the original sheet when it is transferred to its new home.
I have a table of data which represents data in different categories by week.
My ultimate goal is to have another table representing the data for each month - for instance - for each of the categories the data for :
06/04/2009 13/04/2009 20/04/2009 27/04/2009
is summed to make the data for the month of April.
The way I'm doing this at the moment is very long-winded
I'm using a whole new table - the size of the original weekly one - for each month. A calculation decides whether to effectively leave a cell blank or insert the appropriate data based on a date being within a particular range.
So in each "month table" there is the same list of week values:
06/04/2009 13/04/2009 20/04/2009 27/04/2009 04/05/2009 11/05/2009 etc.
but for each "month table" only the cells adjacent to the dates within the relevant month will return actual numerical values within them
This is an example of the forumula I am using in these tables: =IF(AND(($AD7>=MIN($AE$5,$AE$6)),$AD7<=MAX($AE$5,$AE$6)),$C7,"NA()")
Then a master table sums the totals for each month.
I want to be able to keep this table but get rid of the ones for every month as the sheet is getting unwieldy!
I have tried several times to attach the sheet for clarity but each time upon trying to "Submit New Thread " I am getting page not found errors - the sheet is only 133KB and I have tried zipping and sending also - I can't make it any smaller.
I have a spreadsheet in which invoices are listed. Some of these invoices have been cancelled with a negative invoice, so I want to match the negative invoice to the corresponding positive invoice so that accurate averages of all the data can be attained.
I have this table and I want to delete all the table rows that are empty. The problem comes in when I am trying to delete the "Table Row" and not the excel entire row. There is data next to my table and I don't wanna mess it up.
This is the code I managed to put togeter
Code: Sub Delete_Empty_Rows_Table() Dim i As Variant Dim varWorkbook As Workbook For Each i In varWorkbook Set i = ListObjects.ListRows.Count
I need to run a macro to delete all the rows (except for the first 2) in a table. The table is named "Table1". The problem is the tables size is always different. Is there a way in VBA where I can resize the table to the original size?
Im trying to use VBA to copy old entries from a table on one sheet, to a history table on another sheet once they have been there for two weeks.
That much has been accomplished. However, I would also like the old entries to be removed from the original table.
Each entry is numbered using the following method:
If text is entered into the second column in a row, excel takes the number of the entry before that, adds 1 and places this number in a column outside of the table. The first column of each row then references this number. This way your entry is numbered as soon as you type it in. The reason I referenced outside the table and did not type the formula in the first column directly is because when I did and excel deleted a row through visual basic it messed up the rest of the table. Referencing outside fixes that problem, but there is another.
I have several columns for conditional formatting to the right of my table, full of 1s and 0s. When a row gets deleted in the table, all remaining rows shift up but continue to conditionally format based on the cells they were originally along side of. The problem is, those cells recalculate for the row in the table that is now along side of it.
Is there any way I could make the formulas in those columns evaluate only once and to not re-evaluate once something has moved?
