Deleting Multiple Non Contiguous Rows From A TABLE Based On Selection In A Listbox?

Feb 6, 2013

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].....

View 6 Replies


ADVERTISEMENT

Creating Range Of Listbox Based On Selection Made In Another ListBox

May 1, 2014

I have two lists mainly TV Brand & There Models.

List 1 (TV Brand)
Sony
LG
Samsung
Depended List 2 (Models)
Sony LG Samsung
EX420 55EB9600 PL43E450A1FXZP
EX430 77EC9800 PL43E490B4FXZP
EX550 55EA8800 PL43E400U1FXZP
EX520 KN55S9C UN32EH5300FXZP
EX645 55EA9800 PL64E8000GFXZP

I'm using two Listboxes (Form Control) with multiple selection options namely Listbox 1 (Brand) & Listbox 2 (Models). I want listbox 2 input range to be depended on selection made on Listbox 1 (Brand). For example, if user selects Sony then box2 should show only Sony's models and if user selects Sony & LG, box2 should show models for both Sony & LG.

View 3 Replies View Related

Deleting Rows Based On Multiple Filter Options?

Aug 3, 2012

I need to review a 400,000 row spreadsheet and remove all records where 5 columns are populated with data so I am only eft with records that are missing information.

View 9 Replies View Related

Listbox Selection To Filter Pivot Table

Jan 29, 2010

I have a Pivot with a Userform containing 3 cascading Listboxes, each listbox fills down to the next. What i am trying to do is have the result of the listboxes to filter the Pivot table. Keeping it simple for a moment, in listbox1 user has a list of Departments and clicks "Liquor" then the Pivot should only show items within the "Liquor" departments. How do i achieve this? Also when looking at other treads and seeing the code offered, should i be trying to filter the Pivot Table field in the Page or Row area?

View 6 Replies View Related

Listbox Selection And Deletion Of Non-conforming Rows

Sep 12, 2012

I have a listbox on a userform and dependent on which of the 9 values they select, I want to delete the rows in a range that do not contain the string.

I can get the solution using a match on the listbox items but not the reverse, i.e. delete non matches, dependent upon the variety of values that can be selected.

I could potentially use a filter to drop them out, is this the best way? Always struggled trying to build multi filters.

View 2 Replies View Related

Selection Of Rows In A Listbox From Program, Not With The Mouse

Jun 24, 2006

I have two files and I need to compare them. The two files will be store in a listbox an I have to make the lines that are different with another color or select them so it will be easy to see the diferences. It is for one project that have to be ready tomorrow

View 8 Replies View Related

Populate ListBox Based On Selection In Another

Dec 18, 2007

if is possible to make some kind of ComboBox without filters, I mean that let you choose different items at the same time. I do not know if some of you have seen some "ComboBox" that has all the options in one side and next to this window it has a blank window that when you double click the word that was in the other wondow passes to the other window telling you that you have chossen this item.

View 6 Replies View Related

Combobox Multiple Selection Or Listbox Dropdown?

Oct 25, 2010

I have a userform where I want the user to be able to select multiple options, but am having a bit of a problem finding the best way to do this. If I use a combobox, I don't appear to be able to allow multiple selections, but if I use a listbox I don't appear to be able to implement a dropdown facility. I would prefer to have a single line sized box on my form, but doing this with a listbox would probably be confusing for the user as it is not very easy to see what has been selected.

View 11 Replies View Related

Populate Listbox Based On Combobox Selection?

Jun 26, 2014

My table contains 26 columns, I have Column E header as 'Assignee' and Column J for 'Date_Closed'.

I have a form containing a combobox and a listbox. The combobox is populated with 10 Assignee Names.

I want the listbox to be populated with all rows containing the selected Name ONLY if Date_Closed column (J) is blank/null.ie.date not filled yet.

For example, if i select "Ann", the listbox will show all rows in the table that have Assignee Ann for and closed date is still blank.

View 1 Replies View Related

Delete Value In Cell Based On Selection In Listbox

Jun 7, 2012

I have two listboxes where I can move tickers from left to right. This is synced with a sheet, such that the tickers also are pasted there too.

But if I use the remove button, I can remove tickers from the listbox, but I cannet delete them from the sheet. If I understand right the listbox only refer to what row the selected is, not what the text is...

And then its a bit tricky. I have tried a code where it just add the list one more time, after the itmes has been removed. And that could work if it hadn't been for the second column which also has a text, this column is updated after every ticker has been moved from left to right.

What I would like to have is a function that would know what the text in the row that I delete, or remove.

This is what I have so far:

Code:
Private Sub cmdMoveToLeft_Click()

Dim i As Integer, j As Integer, k As Integer
Dim RowsStart As Integer
Dim LastRow As Long
Dim Rows As Integer

RowsStart = Me.ListBoxX.ListCount

[Code] .......

View 4 Replies View Related

Populate Listbox Based On Combobox Selection

Jan 25, 2008

My table contains 6 columns, one of which is 'season'.

I have a form containing a combobox and a listbox. The combobox has 4 options (spring summer autumn and winter). I want the listbox to be populated with all rows containing the selected season. For example, if i select "spring", the listbox will show all rows in the table that have spring in the season column.

I realise this is a very remedial question but i am very new to VBA and programming in general!

View 6 Replies View Related

Adjust Column Label Selection Multiple Pivot Tables Based On One Pivot Table

Aug 16, 2013

I have a pivot table in the first sheet which includes the field "Date" as a column label.

In the remaining sheets, except for one, there are pivot tables based on the same underlying dataset which also include the field "Date" as a column label.

I would like to adjust the selection (i.e., exclude some dates) from the column label in the first sheet and see if it is possible to make the same adjustments automatically to the pivot tables in the remaining sheets as well.

note that the field "Date" is used as a Column label, i.e., it is not a Report filter.

View 3 Replies View Related

Populate Cells From Multiple Selection ListBox & ComboBox

Jan 22, 2008

I need to populate a worksheet's rows with values from 2 comboboxes and a listbox which can have multiply selected items. After finding the row's last free cell, I have the listbox values properly populating correct column. However, I not sure how to get the corresponding combobox values assigned to the appropriate columns.

View 2 Replies View Related

Filter Sheet Based On Checkbox & Listbox Selection

Nov 28, 2007

I have a spreadsheet in Excel, there are 13 columns of information being used. 3 of the columns have just data I typed in (model name, item code, original price) the other 10 have formulas (these formulas are price discounts that will be taken off of the original price. 9 of them also have a check box on the top of the column so if the the checkbox is selected, the formula will give the customer the amount discounted off the original price(keep in mind that not all of the columns can be used together, for example, on product "A" maybe only 3 of the boxes can be used whereas on another product maybe 5 can be used). I made a multiple listbox, so that if a customer selects a product or multiple products and clicks the ok button, the sheet will only show the specific products they selected. My problem is that when I press the "OK" button nothing happens. I don't know how to link all of this together.

View 3 Replies View Related

Multi Column Listbox Change Based On Selection From Combobox

Mar 20, 2012

I have attached my Excel File with the userform I am trying to do. What I am trying to do is change the table in the listbox based on the selection of the combobox and then my selection in the listbox will pass the selected values to Range A1:C1.

Excel 1.xls

View 9 Replies View Related

Add Or Delete Rows From Multiple Sheets Based Table Range

Aug 14, 2009

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.................

View 2 Replies View Related

Deleting Rows From Table Using VBA

May 29, 2014

I would like to delete a given row from a table using VBA

If I use the following Code it works

[DeleteTest].Select
Selection.ListObject.ListRows (120).Delete

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

View 6 Replies View Related

Copy Cells From Non-contiguous Selection?

Sep 12, 2013

If I select a group of non-contiguous cells with control shift is there any way that I can shift the same selection over to the next column. For example, I have A4 and A8 selected. When I press shift and right arrow it doesn't work.

View 4 Replies View Related

Extend Non-contiguous Row Selection In Macro

Sep 16, 2008

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 9 Replies View Related

Copy Non-Contiguous Cells In Selection

Aug 9, 2006

I have a spreadsheet named PRODUCTS and in row A there are a bunch of product codes. I would like for the user to be able to use the CTRL and mouse to select all the products that they want and then once they push that button I would like it to paste the user selected cells into a sheet called Quote starting at A7.

View 4 Replies View Related

Add Items: Multi Column ListBox: Non-Contiguous Range

Sep 6, 2006

i have several listbox with two columns in a userform in a sheet i have several columns whith values and i'm trying to put the values from the columns in the respective listbox. down is the code i wrote but is stressing with column command

Private Sub UserForm_Initialize()
Dim contar, j, i As Double
Sheets("Historico").Select
contar = 0
For j = 1 To 15
linha = j
For i = 3 To 65536

View 9 Replies View Related

Tick Cell Upon Selection. Restrict To Non Contiguous Ranges

Apr 2, 2008

I am trying to use excel to score a test. I want to tick a cell to do so. There is an excellente information about Tick Cell Upon Selection, posted here http://www.ozgrid.com/VBA/excel-checkbox-tick-cell.htm. Its range is limited to (A1:A100)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
Target.Font.Name = "Marlett"
If Target = vbNullString Then
Target = "a"
Else
Target = vbNullString
End If
End If
End Sub

How can I increase the range so it would target C1:C30, E1:E30 ( total of 17 columns)?.

View 3 Replies View Related

Deleting Multiple Rows?

Mar 15, 2013

I have a large list of coordinates that I pasted from a website, and in Excel it has an emty row between every coordinate. I have about 2,000 cordinates, so ~4,000 rows. Is there a way to delete every other row besides manually?

View 1 Replies View Related

Deleting Multiple Rows?

Jan 18, 2013

In my spreadsheet, column A can contain the same word over several rows. If column B is blank at any time - I want to delete all rows that have the same word in column A.

Apple 1
Orange 2
Apple
Peach 1
Peach 2

So in this example - I want to delete rows 1 and 3 that show the word Apple.

View 2 Replies View Related

Deleting Rows Based On Their Value

Jun 26, 2008

I am having trouble with the .SpecialCells(xlCellTypeBlanks)

What I need to do is for a macro to first go through a range ("G8:G50"), Add a 0 value to any blank cell.

Then I want the macro to delete the entire row for any of the cells that have a value of 0 in that range of "G8:G50"

View 9 Replies View Related

Disable Selection In Listbox On Selection Of Item

Apr 4, 2013

I am working on a project where user has to select an item from a listbox, however there is a condition that if user select an item, other items of listbox should be disabled so that he/she can not select any other item.

Is there any way to do that as I tried

VB : Listbox.enabled = False
and
VB : Listbox.locket = True

in Listbox - Click/Change but no desirable results got.

View 1 Replies View Related

Deleting Multiple Rows With Multiple Criteria

Feb 6, 2007

I have a need to delete multiple rows with multiple criteria; criteria contains about 400 different and unique entries that have to be deleted. The code I obtained from this forum was modified but only deletes some entries and bypasses others. Row 1 is header and column 1 contains criteria that it it meets the " Case" it is supposed to be deleted. Example of code is below the Case has been shorted, for posting but normally each case runs the maximum length allowed. Please let me know where I messing up, I am thinking the it needs to be looping until all entries are deleted but I don't know how.

Sub DeleteRecords()
Dim n As Long, lastrow As Long
Dim ty As String
Dim a As Integer
lastrow = Range("D65536").End(xlUp).Row
a = 1 'this is the column in which you will find the notation finance numbers (Col X = 24)
For n = lastrow To 2 Step -1
ty = UCase(Cells(n, a))
Select Case ty..........................

View 2 Replies View Related

Adding And Deleting Multiple Rows

Jul 9, 2013

I have a spreadsheet with multiple similar entries I would like to add up. What would work well for me is to be able to input a column like a and a target column like e.

So in this case it would search throughout column a and wherever it finds a duplicate entry add column e and then delete one of the rows. I could then run it again under different column criteria if needed.

So if I had the below sheet:

I could search by column a and add up column h to read like this:

View 1 Replies View Related

Return 2 Values From Table Selection Based On Value?

Aug 11, 2013

I'm trying to return 2 values from a table selection, based on a value i.e. if I look up the table for "Team 1" - i'd like to return Team 1 v Team 6, Team 9 v Team 1, and so on, to a Fixtures Section in a different area of my Excel Sheet.

However, the look up value "Team 1" could be in column J or N. I've tried Index, Match, VLookup, IF statements etc....

See below:

J
K
L
M
N

[Code].....

View 1 Replies View Related

Deleting Rows Based On Two Criteria?

Jan 30, 2013

I have a column with dates (dd.mm.yy) and I have a column with names. Moreover, several dates and names are repeated. What is needed, is to delete all the rows in which the difference between dates is smaller than 1825 days (5years) for the same name. (Namely, if I have three rows 01.01.1996 - "A"; 01.01.2002 - "A" ; 01.01.2005 - "A" I want all the rows with "A" to be deleted)

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved