I can insert lines fine, but the problem is that now I want the worksheet to be able to delete rows without inputting time stamps. It can insert them with no problems. I have attached the file on as well.
View 3 RepliesI have been playing with some code and was trying an alternate way. My first approach was matching numbers, inserting rows when needed and then deleting rows when needed. I wasn't satisified with this approach because...Well to be honest, I figured it out on my own and I don't think I'm confident enough in my coding skills yet.
Someone suggested that instead of inserting rows, copy the data below the existing data, sort and then do the rest. I did this, but by doing this it gives me over 1, 000 rows of data to loop through and delete duplicates. Yes, my code was shortened and seemed less complex, but it actually took longer with the new approach.
Inserting Rows = 4.6 seconds
Sorting then deleting dupes = 7 seconds
Does deleting multiple rows of data take more time then inserting rows?
Is there a way for a macro to be not active when trying to insert a row or a way to have the macro understand that it's just a row shift? I'm trying to have a time stamp that anyone changes the value in a column. The following code generates an error 1004: application or object defined error when I insert or delete a row.
View 14 Replies View RelatedWhenever you insert / delete row or cut cells out in Mon/Tue sheets it's causing issues in the table in the Weekly view tab.
Is there any way to prevent it? I don't mind redoing whole spreadsheet, it was done ages ago in a very fast manner.
I gave you an idea by deleting a row range in Mon tab which causes #REF error.
I want a script that inserts a picture based upon criteria of another cell. So for instance if cell A1 = Mad then insert Mad.jpg else insert Happy.Jpg
I can get the picture inserted properly based upon the cell however the picture does not delete when I click the sub button again. I cannot make it delete Mad.jpg from it's location and replace it with Happy.jpg if the criteria in A1 says "Happy".
VB:
Sub Picture()
Dim myPict As Picture
Dim myPicts As Integer
[Code]....
The delete function has disappeared from my EDIT menu in excel. Delete and Insert has also disappeared when I right Click on the spreadsheet.
I've written a macro for a set of tests I've created. It says "If the old value of the cell is '-' and the user enters 'Pass,' 'Fail,' or '-' then insert something into the cell directly to the right of the modified cell."
However, I'm receiving a Type Mismatch error any time I insert or delete a row. I think this is because of how I'm getting the previous value of a cell. The conditional is saying "I don't have a solution if there was no previous value of the cell."
Code:
Dim old_value
Private Sub Worksheet_Change(ByVal Target As Range)
prev = old_value
If Not Application.Intersect(Target, Range("E1:E65000")) Is Nothing Then
If prev = "" Or prev = 0 Then
'Do Nothing!
[code]....
I'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 have a rather expansive spreadsheet with inventory statistics and want to insert some cells for an additional calculation and it will not let me add or delete cells. when i select the range to insert the cell my only choices are insert/delete rows.
View 2 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 am trying to use an absolute cell refernce on tab 2 that refernces tab 1.
When I insert a row on tab 1 (tab name is week 1)the refernce changes. For
example cell A4 the formula is ='week1'!$a$4
when I insert a row on the week 1 tab it changes tab 2 formula for cell A4 to
='week1'!$a$5
What can I do to make it not make that change but still be able to insert
rows?
I am attempting to create a template to make time and dollar budgets for various projects easier to create (20 plus projects a year with varying number of employees).
I want to have a template in which you input the number of employees working in a cell (IE. 3 employees), and with that the table expands to have enough rows to have 3 employees data input. However, only insert the number required but not overriding "totals" row below. With the new inserted rows I would like to keep the same format and formula for the row it was based on.
Example :
week 1 hours
week 2 hours
total
[Code].....
On sheet "Create Package" in cell "AA14" I have a value (lets say 2).
I want it to go to sheet "Samples" and insert a number of rows equal to the value on sheet "Create Package" cell "AA14" (so 2 rows)
I have a header row in row 1, so I would like it to insert the designated number of rows beneath that.
Then I would like it to copy a designated number of rows (based off of the "AA14" value, so 2) from the "Create Package" sheet starting at row 66 and then paste special values into the new rows that were inserted on sheet "samples".
I have a problem where my conditional formatting is broken when new rows are inserted because Excel is auto updating the cell references. I am not able to use absolute references because I need to be able to Copy the entire information many times on one sheet to handle an ever changing number of projects.
I have attached an example file which shows a simple version of the sheet. There are areas for two projects now, but more would be added to the sheet as needed by just copying the entire section of one project and pasting it at the bottom. For each project, there is a cell which has a data validation list, e.g. B6, from which the user can select the current stage. The list of stages is obtained from column A, e.g. A9:A18. Whatever stage is selected, I need it to be highlighted in some manner. I currently have conditional formatting that checks if the value in B6 matches the information in the current cell and will change the text font color if True.
The problem occurs when new rows are inserted into the project. For example: if the project requires a Beta 1.1 stage, then a row would need to be inserted and labeled for that stage. However, Excel auto changes the cell references so that it no longer looks at B6. I need some way that the current stage can still be highlighted when selected in the Data Validation list.
I know that what I am needing to do may not be the best method to go about this, but I am having to work within the confines of the software available to me and the intended users of the file. Since the number and length of projects can change on a daily basis, the users need to be able to add and remove room for additional stages and projects whenever they want. I have a basic solution available to me using a macro, but the overall solution is clumsy and just leads to more problems.
I would like to delete rows in a worksheet based on a value of a cell within that row. The worksheet will not always have the same number of rows. I will be looking at the value of each cell in column "AD", which is a date, and if it is not equal to 00/00/0000 then I want to delete that row.
View 7 Replies View RelatedThis is the final code needed...
Col G = Product #
Col H = Description
if Col H ( description ) has 'CVP' or "CO2" or 'CKN' = delete row
unless....
Col G is 617367, 858556, or 882949 then keep that row..
I wish to remove the page headers that were imported along with the raw data. Here's the code I've been using.
Column_To_Check = 3
Start_Row = 1
End_Row = ActiveSheet. Cells(Start_Row, Column_To_Check).CurrentRegion.Rows.Count
MsgBox End_Row
Search_String = "."
For Row_Counter = Start_Row To End_Row
If ActiveSheet.Cells(Row_Counter, Column_To_Check).Value <> Search_String Then
ActiveSheet.Rows(Row_Counter).Delete
Row_Counter = Row_Counter - 1
End If
Next Row_Counter
End Sub
When I import the data from the text file, there are 3 dimensions separated by a period in the same field. I.E: ABC.DEF.GHI and I use the break points to seperate as such: ABC|.|DEF|.|GHI|. Therefore, all rows that contain data also contain a period in cell C, but headers don't.
The Problem
End_Row = ActiveSheet.Cells(Start_Row, Column_To_Check).CurrentRegion.Rows.Count
CurrentRegion only selects up to the first blank row. However, I need this to select the whole sheet, not just the current region.
I have a sheet with 45,000 rows. Let's say each row has 4 columns: Create_timestamp, Update_timestamp, email_address, and o_flag
Many rows have duplicate email addresses. I would like to remove all the duplicate rows, EXCEPT for the row with the most recent Update_timestamp.
And actually, if I could just "hide" all those rows, that would be even better, but I'd be happy just figuring out how to delete all the "old" rows, so I just have a list of unique email addresses, with their create/update timestamps and o_flag column. Seems like this is such a basic use case for "Remove Duplicates,",.
I'm trying to get a VBA macro put together that would delete all the rows, throughout every sheet, and shift up wherever the cell in column A = "Delete Row" in a workbook I've made. In the below copy of the workbook you can see that a lot of the information from all the tables is linked to the topmost table on the 'Mix Detail Sheet'.
If the the Cell in column A is blank on that first table, its corresponding cells in tables throughout the workbook displays the words "Delete Row". I need the macro to delete the rows and shift everything up where this occurs.
Ideally I'd also like the macro to then delete the rows in that first table where the column A cells are blank.
What would be a line of code that could delete rows below a current cell without using absolute row titles? For instance, I pick a random cell and I want it to delete the three below regardless of what cell I pick.
View 2 Replies View RelatedI'm trying to delete a range of rows based on the value of a given cell. For example, if cell AC1 shows 5000, then I'd like to delete (not just clear) the range A5000:AA1048576. Or, if cell AC1 shows 10000, then I'd like to delete range A10000:AA1048576. I've come up with what is below but I don't think this is the right way to do it. I am using Excel 2010.
Code:
Range("AC1").Select
ActiveCell.FormulaR1C1 = "=COUNT(C[-27])+2"
Calculate
Range("AC1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A" & Range("AC1").Value:"AA1048576").Select
Selection.Delete
For some reason I have one column of a worksheet someone else built in which formulas just do nothing - they just show the formula, and do nothing else. My formulas work in all other columns - this column just shows the formula.
I -want- the column to show 00:00:00, which is what it was showing, but apparently in order to show those characters someone disabled the formulas, and I don't know how to enable it again.
I think it might have something to do with the formatting.... Certain formulas seem to work, others don't. My goal is to get it to be 'If A8>0, then 00:00:00(MUST have 6 0's), otherwise ""(blank). I can get a formula to work in it that has 5 0's (0:00:00), but it doesn't seem to obey IF A8>0. I can't even get a basic IF formula to work in it.
I'm fiddling with a C# library, trying to learn about including homemade libraries in my VBA code. I've written a simple library, compiled it, and did the COM registration. When I open the VBE and start up a new module, I'm able to find the library in the Tools | References dialog box, and I check it to include it. When I write the code, however, autocomplete doesn't give me the option of any of the definitions I've written into the library, and when executed, I'm getting a "User-defined type not defined" error.
View 1 Replies View RelatedWill excel allow text to be permanently be positioned in a specific cell even if rows or columns are added?
View 6 Replies View RelatedWindows 7, Excel 2010
I have an add-in with UDFs. I want explanation of the function arguments in the function wizard and also be able to click for Help in the wizard. So I register the functions like this:
Application.MacroOptions macro:=Range("afsfunctienaam")(i), _
Description:=Range("afsfunctiebeschrijving")(i), _
Category:=Range("afsfunctiecategorie")(i), _
HelpContextID:=Range("afshelpcontextid")(i), _
HelpFile:=HelpPad, _
argumentdescriptions:=Sheets("afsnederlands").Range(Paramtabel)
Where Param is the address of an array with argument descriptions, as text (like "A1:A4").
If I run the xlsm file, it works fine.
But if I load the xlam file in the add-ins dialog, the path to the Help file seems to be lost; I get directed to Excel's general online Help. The explanation of the arguments still work. Probably because they are already in the registry. But shouldn't the Help path be held in the registry as well?
I have a company with upto 5 products, and I have created a dropdown box where you select your product.
When I select product 1 i would need info on product 1 , when i select product 2 I need info on product 1 and 2 and continue. so for product 5 -i need info on product 1-5.
I tried to add all info on excel and tried to delete it one by one by deleting rows in excel for products but it does not work.
I have inherited a spreadsheet with over 800 rows (and daily expanding) and 14 columns of data. I've attached a sheet that looks similar, with only 200 rows for reference. Here are my questions:
1. How can I delete all rows that contain no values?
2. I want to fill an entire row of data yellow if the values in the final two cells (L&M) in the row are equal, and red if they aren't. How can I do that?
3. Column A contains only dates, from oldest to newest. I'd like an obvious visual clue for when the months change. Currently it's a long, merged, blue-filled cell that says "March 2011", for instance. It can't be color because all cells need to be filled based on certain criteria (see #2), and borders aren't obvious enough.
4. I want to click on the row number on the very right, but have it select only columns A-M, is that possible?
I'm also having trouble with autofill. It seems to only work on parts of the spreadsheet. I don't know what settings may have been changed, but I do have autocomplete turned on, and no clue what to do despite hours of googling.
Test Tracking Sheet.xlsx
I have cells (all in one column) containing text separated by commas e.g. (SD-299, SD-200, SD-300)
I am trying to transpose the text in these cells into rows.
VB:
Sub SplitAndTranspose()
Dim N() As String
N = Split(ActiveCell, ", ")
ActiveCell.Resize(UBound(N) + 1) = WorksheetFunction.Transpose(N)
End Sub
The problem with that is that when transposing it does not shift the cells down (/ insert new rows) so I lose the data already in the cells underneath.
Also, that macro would only apply to one cell I would like to be able to apply it all the data in the specific column on my Sheet.
This Code is Pasting "A1" into the destination - format and all. I need it to only paste in the value.
Sub test()
Dim lastrow As Long
lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Sheet2").Range("A1").Copy Destination:=Sheets("Sheet1").Range("A" & lastrow)
Application.CutCopyMode = False
End Sub
How do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies View Related