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?
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?
I can't understand how Delete works when applied to a table.
Here is my table: Number Letter Test
[Code]....
If I use "EntireRow.Delete", I have an error. If I use "Delete" only, it works for A and B, but for C, it suppresses the Letter column.
By the way, I don't understand why I have to include header (Table1[[#Headers],[#Data],[Letter]]) when I filter to find unique letters at the beginning of the script.
I have two worksehets. The first worksheet contains data on products. The product code is in column A. My second sheet contains a list of product codes in column a. I want to delete all rows in worksheet 1 where the product code doesnt exist in the list in worksheet 2. Can someone provide a outline for a macro to loop over sheet one and look up the value in worksheet 2 and delete the row from worksheet 1?
I would like to achieve is a Yes there are Duplicates & NO there aren't any in Column H. If Column A,B,C,D & E ALL Match & there is another Row that Exactly Matches A,B,C,D & E Then Return a YES or NO in Column H. Though I DO WANT ONE ROW TO STAY as I will Delete the Rest of the DUPLICATES.
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 have a range that has been formatted as a table. Once I've applied a filter, if I try to select all of the rows, and delete the visible rows the option is greyed out.
Is there no way, short of vba, that I can just simply delete the visible rows?
a VBA problem I have and I do not know how to solve. I use Excel 2003.
I have a workbook with a table; all cells except the table area are locked so at users cannot add any data. The table is on D2: H?. The workbook is password protected (users can only "Select unlocked cells", "Format cells" and "Sort"), and for that reason there is a "Add Rows" macro button so they can add more rows if necessary. It works well.
There is also a "Delete Rows" button, but it does not work as I want it to do. If the table is for example on D2:H4, and I select a cell on row #3 (ex. D3, or E3, or H3) and click on the button it deletes row #3, if I click on the button again it deletes row #4, and if I click on the button again it also deletes row #5 and so on, but it should not do it because all rows below row #4 are locked and should not be deleted.
I found on the Internet a code that I modified, but I do not get it to work. Below are the two macros.
My original macro (it deletes even locked rows):
Sub DeleteRows() ' ActiveSheet.Unprotect Password:="123" ' Selection.EntireRow.Delete ' ActiveSheet.Protect Password:="123", DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingCells:=True, AllowSorting:=True End Sub
The macro found and modified (the original rows are commented):
Sub DelRows_2() Dim rngDelete As Range Do While rngDelete Is Nothing On Error Resume Next
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.................
My worksheet has a report name in cell A1, a brand name in B2, price name in C2, and color in D2. Row 3 has a table header in cell range A3:E3 followed by rows of table contents at the end of which is a row of empty cells. This sequence repeats itself for a new product: a row with the report name in column A, followed by a row with a new brand name, price and color in the same columns as before followed by the same table header and table contents. The number of products (with the previously mentioned details and table) vary so I would like to write a macro that is not dependent on a set number of products. Each table varies in the number of rows but never contains empty cells and always ends with a row of empty cells. I want to delete all rows with the report name and have all products in one table with the brand, price and color added to the table header and contents.
I have the code below which is doing all I need it to do, except I would like to add a line that deletes all rows where the cell value in column "F" is a number:
The main thing is I can delete all rows if the value in column "F" is a number.
Idea Being, it: -Deletes all rows where value in column F is blank. -Then Deletes all rows where value in column "F" is numeric. -Then deleted all rows where value in column "F" is any value specified in Column A is sheet 2.
I have a workbook has many worksheets, I would like to be able to delete worksheets if, for example, the number of rows in the worksheet is less than 100 rows.
The following macro deletes blank rows in my project (I have a source sheet that I "transfer" information to various "law practice worksheets", and it works fine. However, once I get past a certain number of rows (my project will include 65,000 rows), the macro does not delete the blank rows any more from the "law practice worksheets" (I previously sent this spreadsheet to the Forum with another question that was not answered yet). Here is the macro for deleting rows:
On Error Resume Next Sheets("InternatlLaw").Select Range("A2:D65000).Select Application.DeleteBlankRows = True Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete ActiveSheet.Range("A2:D65000").Select On Error GoTo 0.............
I am trying to write a macro to clean up a CSV file which automatically hard codes titles throughout the spread sheet. I am getting an error on the "FindNext" method below. I am getting "Unable to get FindNext propety of the Range Class" error. Also, the code for the For loop for multiple lines does not appear to be working.
Sub cleancsv() lastRow = Range("A1").End(xlDown).Row pmpt = InputBox(Prompt:="What text are you looking for?", _ Title:="Text", Default:="i.e.: Finished Goods Inventory") numrows = InputBox(Prompt:="How many rows to delete (counting original):", _ Title:="Number of Rows", Default:="i.e.: 1") Set cell = Range("A1:A" & lastRow). Find(pmpt) If Not cell Is Nothing Then For l = cell.Row To cell.Row + numrows Range("A" & l).EntireRow.delete Next l End If Do
I have 3 columns of data "Store", "Week" and "Cust" and use the code below to ensure that it was always 150 consecutive rows long regardless of whether any data is in the row.
This is fine if I just have one week to sort, but I find I now have 52 weeks of data and each week could have a different number of stores. I just need the macro below to run each time a new week starts. I can easily put a space (or something else) at the start of each new week so that a " loop" can check for it.
For i = 1 To 150 If ActiveSheet. Cells(i, 1).Value <> i Then ActiveSheet.Rows(i).Insert End If Next
I'm new to using VBA and have been recording macros and then trying to edit them where I need to and using Google to get the answers that I need, however I have hit a snag when I create a pivot table using my current macro.
The data sheet that I am creating my Pivot Table from will always have a different number of rows from week to week, but I am unsure of how to code this into my macro. When I recorded this, I used the range A1:S10000, which gives me (Blank) as the last row in my pivot table. change my code so that it only selects the data rows to stop these blanks appearing?
Code:
'Pivot Table DATA Tab to show Call Out Times for all Centres in file ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "DATA!R1C1:R10000C19").CreatePivotTable TableDestination:= _ "PIVOT!R3C1", TableName:="PivotTable1", DefaultVersion:= _
I have a column C which list Unique ID and this ID can be repeated several times in Column C. For each row the Unique ID has a value in Column D of how many sick hours are taken. I would like to create a pivot table which would provide a count of the Unique IDs and the total number of hours for the Sector in Column A.
Above formula, in cell E8 is the number 3, however excel does not recognise this and returns 1. is there a way i can get E8 to see that it is a 3 in that cell and return the correct number of rows downwards in my table?
I am trying to populate the 2 tables from excel to word. I will be getting the excel file with tables in various sheets. One sheet consist of 2 tables that will be inserted to one word document. So if there are 2 sheets then I will have the tables inserted in the 2 word document. In the excel sheet I have attached, there are 2 sheets with tables in each of them. I have written the code to copy and paste the table to word doc from (general) range A1:G4 (Table 1) and A9:H18 (Table 2) that has empty rows and columns selected. But there are empty rows and columns inserted since the table range is not same sheetwise. I would like get the empty rows and columns deleted in the word table.
Find the attached sample excel sheet and the word documents.
I have working code that returns a row number within a for loop based on parameters I set.
Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.
Code: for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row) if x = y then *storedRow = rowNum end if next rowNum *
Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.
All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.
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?
I'm making a Excel 2013 spreadsheet that has formula in a column that auto enters a number 1-40 when something is entered to the left of that cell. There are 300 rows in the spreadsheet. I would like to make a drop down list in a column cell to the right that would delete that number in that cell from the drop down list. For example cell C1 has 39, that 39 then is deleted from the drop down list. C2 has 22 in it, click on the drop down list cell and it shows 1-40 less 39 and 22.
Is there a function to convert the data shown in table 1 to table 2 without rearranging the columns and rows? because i don't want to use TRANSPOSE. I want a function, somthing like SUMIF with OFFSET or INDEX and MATCH or any other function.
removing duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
