Ensure Selection Row Deletion Doesn't Extend Past Certain Row
I have a delete button on a protected sheet to allow a user to delete selected rows (unprotects, deletes, protects again...).
I need to add a check to make sure they do not delete any row greater than 152. How can I check if a row greater than 152 is in their range of selected rows?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Extend Non-contiguous Row Selection In Macro
I get 40~100 page PDFs of purchase orders every week. Each page has one part number and its open order and forecast information. Some pages have a few line of information, some have many lines. I convert this file into a txt file. I then create a macro to import it as space delimited and format it. What I need to do it get rid of the header/footer information on EVERY page. When the txt file is created it doesn't have any page marks. The header has "ABC Widgets" as the customer name and then 10 rows of unnecessary information. If I do a Find ALL for "ABC Widgets" and select all, it will delete that header line at every instance, but the leftover selected cells then start not-lining up (meaning I can't just hit "delete row" 10 times and have it pick the right row--the rows don't line up any more and each instance is -1 row offset.) Is there any way to extend the selection of non-contiguous rows (starting with ABC Widgets) down by 10 so I can delete them?
View Replies!
View Related
Ensure TextBox Entry Doesn't Exist In Column
I have the following code that enters data from a user form, the problem is that the textbox (Locker) data will not validate when entered into worksheet. Private Sub cmdEdit_Click() Dim rownum As Integer rownum = 2 'Prompt user with message box asking for input in both text boxes If Me.txtNumber = vbNullString Then response = MsgBox("Please enter a Work Number", vbInformation) Me.txtNumber.SetFocus Else ' Insert the work no., driver, locker, keys issued & keys On hand ActiveCell = Me.txtNumber.Value ActiveCell. Offset(0, 1) = Me.txtLocker.Value ActiveCell.Offset(0, 2) = Me.txtIssued.Value ActiveCell.Offset(0, 3) = Me.txtOnHand.Value................
View Replies!
View Related
Delete Row If Doesn't Contain Certain Numbers
Working at a company where we every month extract our customers into a excel document. This is the CRM system , and some of the clients can't (shouldn't) be approached. In Cell C is their customer classifications. In total there is about 60 different classifications. 10 of them is clients who we can send information to. For example customers with class 11111 or 41414 or 51515 or 61616 (among with 45 others) can't be approached. I'd like a macro/script who can delete all the nonrelevant clients so that afterwards I have a list of clients who can be approached.
View Replies!
View Related
If Cell Doesn't Start With Asterisk Delete Row
Sub DeleteRowsBasedOnCriteria() 'Assumes the list has a heading. Dim cl As Range For Each cl In Range("A6", Range("A65536").End(xlUp)) If cl.Value <> "=~*" Then cl.EntireRow.Delete End If Next cl End Sub but it is deleting every row, I am not sure what is wrong? starting in cell A6 if cell does not start with *, then delete entire row, next cell that is the logic im seeing...ive tried a few o ther combinations like "~*" etc...
View Replies!
View Related
Conditional Formatting For Row Banding Doesn't Work After Autofilter
For obvious reasons, the conditional formatting to shade alternate rows doesn't work when filtered. So I think I need another way of doing it. Luckily, my table is fairly static, rows aren't added or removed. The first column is excluded from the banding. As is the first and last row with data (1 & 67) respectively. I found a relevant thread here, but the code is beyond my understanding.
View Replies!
View Related
VBA Doesn't Recognise Selection
I have a report that has been running in it's current state for several months with no issue. However, today it has decided that it doesn't understand the word 'selection'. eg: selection.copy gives compile error. Now... obviously NOT USING selection is preferred. However, let's put that aside for now because there are LOTS of these in this report and it's never had an issue until today. Is there some setting that could have gotten changed or something? It's happening on multiple computers and we're running Excel 2003 (standard) with SP3.
View Replies!
View Related
Macro For Copy/paste Selection Of A Row To A New Row On New Sheet
I'm new to excel and VBA and I'm having problems with a copy paste macro that I’m using to transfer information from Sheet1 to sheet 3. The macro must: On the click of a command button copy information from A5 to M5 and A6 to M6 and paste it to the next 2 free rows on sheet3 between columns A and M. After the copy/paste the macro must clear the information from the input sheet - A5:M5 and A6:M6 - but retain the formatting.Thank You.
View Replies!
View Related
Row Deletion - Advanced
I am working on a large Audit project for one of my many bosses and I am a little stumped so I came to the experts. I have a list that is organized randomly and we need to have 95% of the cells deleted. I don't want to have to go through each time and calculate that out each time. I am sure that I would have to find the last cell used in one of the columns and then multiply that by 95% and then delete from that row down in the sheet. (Since the sheet is already organized randomly it can delete the bottom 95% of rows.) I am not sure how to put it all together.
View Replies!
View Related
Row Deletion Per Criteria
I have an Excel sheet that is similar to the following: Customer----- contract ----- value XYZ ------- 123 ------------- $50 ABC ------- 987 ------------- $150 XYZ ------- 123 ------------- $-50 XYZ ------- 123 ------------- $200 In the above example, I want to delete Row 1 and 3... it is an accounting issue, and when the values of the same contract match (50 and -50) then it needs to be deleted from the Excel sheet (whole row). I want to leave rows 2 and 4.
View Replies!
View Related
Disable Row Deletion
Excel 2003 - VBA - How do you detect when a row has been deleted in VBA? Better yet, how do you detect when a row is about to be deleted? I also would like to know the row number. I do not see an event handler that will inform me of the "Delete" event.
View Replies!
View Related
Conditional Row Deletion
I have read several posts on this subject as well as the two common methods recommended by Dave. Hovever, I have not been able to incorporate the other thread recommendations to my specific problem. I want to delete entire rows based on the absence of a matching criteria from a list on another tab. Example: Tab1 contains a list of names in A1:A15. Tab2 contains rows of data wherein column C lists names that may or may not be in the list on Tab1. I want to delete rows that have a name in column C that does not match any of the names in the list on Tab1 A1:A15. Here is the code I have pieced together thus far: Sub Delete_elimntd_players() Application. ScreenUpdating = False Sheets("QB").Select Range("C2").Select Do Until ActiveCell.Value = "" If ActiveCell.Value <> 'NEED CODE HERE' Then ActiveCell.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If Loop Application.ScreenUpdating = True End Sub
View Replies!
View Related
Deletion Of Row Conditional?
I have been pulling my hair out for hours now trying to figure this out. I want is to figure out is how to compare an address in Column C to an Address in Column H, If they don't match I want excel to delete the row. I have 7500 rows to compare. I have read lots of posts and looked at the code for delete row conditional but I don't seem to be able to put it all together.
View Replies!
View Related
Copy Between Worksheets Doesn't Work (look For A Certain Value In Worksheet A And Copy That Row Of Data To Worksheet B)
look for a certain value in worksheet A and copy that row of data to Worksheet B. However, it seems to be only copying the row in worksheet A and pasting it. Is there something that a noob VBA scripter has missed out? PHP Private Sub GetInfo_Click() Dim r As Long, LastRow As Long, Status As Integer Dim Message As String, Title As String, Default As String, MyValue As String Application.ScreenUpdating = False MyValue = Range("A4").Value Workbooks("invoice.xls").Worksheets("A").Activate LastRow = Range("C65536").End(xlUp).Row For r = LastRow To 1 Step -1 If Cells(r, 1).Value = MyValue Then Rows(r).EntireRow.Copy Workbooks("invoice.xls").Worksheets("B").Activate Rows("8").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Status = 1 Workbooks("invoice.xls").Worksheets("A").Activate Rows(r).EntireRow.Delete Exit For End If Next r Application.ScreenUpdating = True
View Replies!
View Related
Min Row And Max Row Of A Selection
I want to program a macro that reads out the min row number and max row number of a selection. Lets say a user selected the Range A1:D4. After executing the macro I want the following output: The min row number of the selection is: 1 The max row number of the selction is: 4
View Replies!
View Related
Speed Up Row Deletion Array
I currently have a macro set up to delete rows if a certain user selected value is not found in a certain column. It works fine if the sheet isn't overly large but the problem is we have some spreadsheets with 25,000+ rows and it takes time to loop through - I'm not sure if there is even a way to make it faster. Currently it works by looping backwards on the sheet checking each value in the cell versus an array containing the user selected values. If a match isnt found it deletes this row. 'y = long value representing row 'x = counter for each item in user selected array 'wsSheet = worksheet we are using 'rowLast = last row on spreadsheet 'arrSearch() = string containing user selected items i have screenupdating/calculations/events turned off - i just didnt know if there was a faster way of looping through i just thought about instead of doing an array maybe joining the array into 1 string and using a "like" comparison check to see if a match is found - would save me from having to constantly loop x * y amount of times - not sure if this would work or not. ill post back if it doesn't
View Replies!
View Related
Multi-Conditional Row Deletion With Dates
Here are the examples (pictures): Example 1 Before Example 2 Before You can see that it's an history of currency trades (Forex). What I want is to see how much money one would have made letting only one open position per currency pair. A basic algorithm would be something like this:
View Replies!
View Related
Update Sequential Numbers After Row Deletion
How do I get my worksheet (ServicePlan) to update column A (plan number) after deleting a the row from a userform. When I delete the row the plan numbers do not update. Example: I delete plan #1 from the worksheet. The plan number #2 should now be amended to read plan #1 and the plan number for record #3 should now be amended to read Plan #2, however the plan numbers #2 and #3 remain the same. Plan #Rv# Name:Dept: Rep: 11.1mikeEnvironmental Cathy S. 22.1TomCasualty Rich A. 3 1.5 Michelle Special Programs Diane Here is the code I used for the delete plan # button. Thank you Private Sub CommandButton15_Click() If MsgBox("Are you sure you wish to delete this record?", vbYesNo, "Confirm Deletion") = vbYes Then sheet1.Activate Dim strFind Dim Nullstring Dim rSearch As Range 'range to search Set rSearch = sheet1.Range("A2:A1000") Dim c Dim r As Long
View Replies!
View Related
Multiple Criteria Row Deletion Code
modify the delete row macro below. Unfortantely this is where my brain cell sometimes dies and the easy answers are as apparent as they should be... Ideally what I am looking to do is have a growing list of "IDS" in one worksheet (call it delete IDS) that will be used to loop thru the sheet named "NRs" and delete the the IDS from that worksheet. Sub DeleteNames() 'using set column Sheets("NRs").Activate Dim RngCol As Range Dim i As Range Set RngCol = Range("A:A") For Each i In RngCol If i.Value = 156916233 Or i.Value = 600093 _ Or i.Value = 156970571 _ Or i.Value = 156948409 Or i.Value = 30560 _ Or i.Value = 163841503 Then _ i.EntireRow.Delete Next i End Sub
View Replies!
View Related
Highlighting Active Cell's Row, Along With Any Row That Shares Same Value In That Column
Is it possible to click on a cell in column C, and have the wishlist below happen: That active cell's row is hightlighted. Any cell in that column that has the same value as active cell is also highlighted. Plus, any cell in another sheet that has that value it's row is highlighted too. Example: I click on C5 in Sheet 2 its value is 45000789 it row is highlighted, this value also appears in C3 in the same sheet, so it's row is highlighted as well. Plus, in sheet 1 in C10 this value appears and it's row is highlighted as well. When any of the values are clicked again the highlight is removed from all parties.
View Replies!
View Related
Speed-Up Multiple Row/Column Deletion Macro Code
I have the following function in a worksheet module: Private Function NixEmptyColumnsAndSuperfluousRows() Me. Range("A1:A7").EntireRow.ClearContents Me.Range("A1:A7").EntireRow.Delete Shift:=xlUp Me.Range("BE1").EntireColumn.ClearContents Me.Range("BE1").EntireColumn.Delete Shift:=xlToLeft Me.Range("AT1").EntireColumn.ClearContents Me.Range("AT1").EntireColumn.Delete Shift:=xlToLeft Me.Range("AK1").EntireColumn.ClearContents Me.Range("AK1").EntireColumn.Delete Shift:=xlToLeft Me.Range("Y1").EntireColumn.ClearContents Me.Range("Y1").EntireColumn.Delete Shift:=xlToLeft Me.Range("O1").EntireColumn.ClearContents Me.Range("O1").EntireColumn.Delete Shift:=xlToLeft Me.Range("K1").EntireColumn.ClearContents Me.Range("K1").EntireColumn.Delete Shift:=xlToLeft..............
View Replies!
View Related
Extend Selection From Active Cell To Desired Column
1st post so hope that title isn't too vague. Using VBA, I have a macro that will find a column based on a week number and add in a new column. It will then offset the ActiveCell down one. I now need the macro to SUM all values in that row to the left of the ActiveCell. My original thought was to use: ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlToLeft)).Select Unfortunately, there are gaps in the data field, blank cells that should count as zero value. How can I highlight all cells to the left, from whichever column the active cell is in, through to column B?
View Replies!
View Related
Run Selection Change Event Only If The Row Selection Changes
I'm using the following code in a worksheet: Private Sub Worksheet_SelectionChange(ByVal Target As Range) col = ActiveCell.Column Range("output") = ActiveCell.Offset(0, -(col - 4)).Value End Sub In case it's not obvious, the macro places the value in the active row and 4th column of the worksheet into the range "output". The problem is, the worksheet is large and somewhat slow to recalculate. This macro forces a recalc on any selection change, but i only need it to run when the row selection changes, not the column. I'm sure there's a straightforward way to reprogram this.
View Replies!
View Related
Average Row Of Numbers Using Different Row #'s
Field 1 input: Beginning Date (e.g 7/17/09) Field 2 input: End Date (e.g. 7/23/09) Need: Average costs on and between these given dates From table with this information: Date Market two 7/16/2009 80 7/17/2009 80 7/20/2009 82 7/21/2009 84 7/22/2009 82 7/23/2009 82 7/24/2009 82 7/27/2009 82 I need Excel to average Market two 7/17 to 7/23 (80, 82, 84, 82, 82) based on the dates I give it. If I change the date to 7/20 to 7/24, I then need excel to average (82, 84, 82, 82, 82) How can I get Excel to average based on different dates? Or basically, how do I get it to pull rows between 2 inputs?
View Replies!
View Related
Hlookup To Skip Past First Found 'lookup_value'
I'm using the following formula "=HLOOKUP($B$4,Arg!$D$10:$DA$50,5,FALSE)" With Cell $B$4 as the 'Lookup_value' I wish to pull the second instance of the 'lookup_vlaue' not the first from within the 'Table_Array'. Later on I'd like to skip to the 3rd column/instance etc. It's not practical for me to change the size of the 'Table_array'
View Replies!
View Related
Ensure/Force Selection Of Single Point Of Chart Series
I have an XY chart that I would like to have the user select a single data point in the chart and be able to add a comment in a table corresponding to that point. For example: if the x-values are in A1:A10, y-values are in B1:B10, the user can select a point, say the 4th point, have some code run that will enter a text string into cell C4. I can manage the code to add the text to the appropriate location, but I'm having trouble with 2 things. First, how can I ensure that the user has selected a single point (as opposed to the entire series or the chart background)? And second, how can I determine which point is selected? I think I need to determine the point's item number in the SeriesCollection, then offset that number of rows in my data table. But I can't find a way to retrieve that number.
View Replies!
View Related
Restrict Combobox Selection To List Only But Allow Deletion
I have the following code which works but after the user gets an initial error says that they have to enter what is a ComboBox choice they get a second error when they press backspace to delete their entry. Is there a way to just give them the first error but not an error when they press backspace to erase their mistake in the form? This checks the user input... Private Sub Combobox5_Change() If ComboBox5.ListIndex < 0 Then MsgBox "Please Only Pick From The List. Use Admin Page to Add More to the List", vbCritical, "Error" End If End Sub ...........
View Replies!
View Related
Sorting Certain Columns But Ensuring Certain Rows Remain Fixed To Their 'master Row'
I have created a spreadsheet which is tallying up various data. I have a number of different categories e.g. A, B, C, D and within each one of these are sub categories. I then have a tally for the number of each category and within that the number of each sub category. I then want to sort the data so that first the main category is sorted in order of highest tally to lowest. the sub categories however need to stay with their primary category. I then need to sort each sub category within the main category. the data looks omething like this: category tally sub cat number A 6 A 3 B 4 C 6 B 9 A 1 B 6
View Replies!
View Related
Insert Selection To Next Available Row +1
I have a table in rows 19:39, and I have assigned this macro to a button. If a new table needs to be inserted "just press the button", and I would like it to insert a new table below the last table created. So, I thought it was Selection.Insert Shift:=xlLastRow + 1, however, this just seems to push down the previous table created by pressing the button. (I have more code that only formats the table after insert, but am not showing it here, since for testing I have 'd it all out). Thanks for any help in advance. Rows("19:39").Select Selection.Copy Selection.Insert Shift:=xlLastRow + 1 Application.CutCopyMode = False
View Replies!
View Related
Dynamic Row Selection
Here is my code piece ================ For pCounter = 3 To OutputWorkbook.Worksheets("abc").UsedRange.Rows.Count If temp = outputWorkbook.Worksheets(Output). Cells(pCounter, 1) Then rowToColour = pCounter + 1 outputWorkbook.Worksheets(Output).Rows("rowToColour:rowToColour ").Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With End If Next pCounter ----------- Explaination ----------- -I store some numeric value in "temp" variable in some other part of my program - Then i need to search the "Output" sheet to find the Cell in which i have the same value stored as the string "temp" - And once i have found the cell , i need to colour the row beneath that cell with red. The issue is with selection and colouring of the row after finding the cell. So the question is : Is this the right way to select the row ? i keep getting errors like 1004 etc.
View Replies!
View Related
First & Last Row Of Selection
I am trying to format a range of cells based on a user selection. The user may select 8 rows (rows 20 - 27) or the might just select A20 to A27 or the may select A20 to C27 on all of the occasions I would like to find the 1st row and the last row of a selection. I have found the command x = ActiveWindow.RangeSelection.Address which I could try and extract the row numbers, but is there an easier way?
View Replies!
View Related
Doesn't Work If The Worksheet Hasn't Been Saved Yet
I'm trying to simplify a formula that I'm using in a spreadsheet that's being used mostly in Excel 2000. 2 reasons I'm doing this: 1) It doesn't work if the worksheet hasn't been saved yet. 2) It's ugly =DATE(YEAR(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3) & " 20" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+5,2))),MONTH(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3) & " 20" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+5,2)))+1,0) The formula returns the last day of the month of the name of the sheet. For example, if the sheet is names "Mar 07", it returns 3/31/07. (yeah, when the 2100 issue becomes a big deal, I'm not going to be ready but I have 92 years to think about it) Even if I can't make it prettier, does anyone have a way to get the sheet name into a formula without VBA Code in a file that hasn't been saved.
View Replies!
View Related
Variable For Row / Column Selection
I need to create a macro that clears the contents of a range of cells. However, that range of cells is variable. I know my starting cell (C9) and I know the selection goes over to column Z every time but the number of rows I want to clear after that may change. Also, whatever that first cleared range is, I would need to skip the row below it then clear the same number of rows below that. (not entire row of course, just the ones from column C to column Z)
View Replies!
View Related
Toggle Row Highlight On Selection ON/OFF
I inserted the code below in my worksheet which allows me to highlight the entire row of any cell I click into. I use it when I am entering data into different cells along the same row. Helps me keep things lined up. Is there a way to turn this on and off? Maybe a button or check box that can be clicked to activate and deactivate it. Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveSheet.Rows(Target.Row).Select ' highlite entire row Target.Activate ' select the cell End Sub
View Replies!
View Related
Delete Associated Row From Listbox Selection
My userform has an update button that displays a listbox in a userform. The user selects from the list, chooses to update and another userform appears to request some additional information before saving it to a different sheet. This works fine, but how do I then delete the row containing the original item on the original sheet selected from the listbox at the same time?
View Replies!
View Related
Copy The Information From The Row Directly Above The New Row And Paste (values, Formulas, Formats, Etc) Into The New Row
1. In whatever cell is selected when the macro is run, enter a new row. 2. Copy the information from the row directly above the new row and paste (values, formulas, formats, etc) into the new row. 3. Return to column P in the new row, i.e if the new row is row 11, then return to P11, for row 12 return to P12, etc. I have tried recording the macro but because it is hard coded to specific rows, its not working. I have attached a sample copy of the sheet (had to zip due to the size of the file).
View Replies!
View Related
Delete Entire Row Based On Selection
I have a worksheet where I have restricted the users from scrolling around beyond a certain range. Because of that the user is prevented from selecting an entire row. There are some cases where the user should be able to delete entire row or range of rows. How can I allow them to select say cells "C17:C35" [they would just hoghlight the range] and hit ctrl+d; which would trigger a macro and the macro in turn would delete all the rows in that selection [rows 17 through 35]? I know some basic codes for deleting entire rows, but I don't know how to read first and last row of the selection under Target property.
View Replies!
View Related
Delete Row Based On Range Selection
Here is my ThisWorkbook.sheets("sheet1").Range("M").Select For Each cell In Selection If cell.Value = ThisWorkbook.Sheets("Sheet2").Range("A").Value Then cell.EntireRow.Delete End If Next cell Range("a1").Select End Sub I want to remove all rows in sheet 1 that contain any value found in Sheet2 A I using XL 2003.
View Replies!
View Related
Return Address,Value,Column/Row Of Selection
I am wanting to allow the user to select a cell in a worksheet and return the cell address and information/value from the cell. For example, if "Dog" is in cell A1 and "Cat" is cell A2 and the user initiates the Macro I would like the macro to to pop up a message box and say something like "Select a cell in Column A". When the user clicks on cell A1, I would like the message box to pop up and say, you selected "Dog" in cell A1.
View Replies!
View Related
Populate Form After Selection Of Row In Textbox
What I've done so far is... Created two buttons (Add and Find) in my Excel sheet1. If I click my Add button Form1 opens. In Form1 the user enters a number of values that are inserted into sheet2 of my Excel book. If I click my Find button Form2 is displayed. This form contains a multicolumn textbox that shows some of the columns from sheet2 that have values in them. Now to my BIG problem.... I would like to be able to select a row in my multicolumn textbox, click my OK button there and then Form1 should be displayed with ALL the values from that particular row from sheet2. As I mentioned I'm very new at this and I'm not even sure how to do this logically and much less the correct code for it. I hope that someone could thoroughly explain to me what I have to do and why to be able to get this working. Private Sub UserForm1_Initialize() Dim rIds As Range Dim MaxId As Long Set rIds = Worksheets("Systemtest").Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) MaxId = Application.WorksheetFunction.Max(rIds) With Me .IdBox.Value = MaxId Private Sub DateBox_Change() DateBox = Format( Date, "yy/mm/dd") End Sub.....................................
View Replies!
View Related
Getting Value From Next-highest 'header' Row
I have a worksheet that contains a number of rows. [Thank you, Captain Obvious!] These rows are logically grouped to represent a series of "items", with each item having one or more rows (with a "header" row at the top). The rows for a particular item represent different types of information about that item, and therefore make use of different formulas based on the row type. Due to the relative complexity of the formulas in these rows, I am creating a series of row "templates" from which I can copy and paste every time I want to create a new item or add rows to an item. As such, I am trying to avoid direct cell references to different rows, as these would easily get messed up during cut and paste. Instead, I am trying to limit myself to various lookup functions (e.g. OFFSET, MATCH, etc...) that will work regardless of where a row is added. In order to accomplish this I am using a "key" column to group all rows for a particular item together. Until now I have had to type in the key value for every row for a particular item. What I would like to do is to only have to type the key value in the header row, and have a formula populate the key value in all subsequent rows ... until a new header row is reached, at which point the new key value will be used. Take a look at the following example: ....
View Replies!
View Related
|
TRACKER
