Removing Column(s) From Selected Range?
May 8, 2014
I have the following code that selects a range based on the rows that have data in the sheet, starting with row 10:
[Code]....
This works well, but by default it captures all columns with data. I'm not sure how to then change it to only capture specific columns.
For example, if I only wanted columns B:D for rows with data, changing it to
[Code] .....
does not work, because the SpecialCells selection overrides that and still returns all columns with data.
Is there an easy way to drop column(s) off of the currently selected range after SpecialCells captures the range?
View 1 Replies
ADVERTISEMENT
Jan 26, 2010
Attached is a sample sheet with nine columns. Column D includes a name, but in many cases it is preceeded by: 'TO', 'BY', or 'OF' and a space which I need to remove (if they exist), leaving just the name. Please note that sometimes just the name exists so nothing needs to be done. I believe that in a jet sql querie I can use something like:
View 7 Replies
View Related
Jun 23, 2014
I have a couple of listboxes and use the mouseup event to do stuff with the row that's clicked.
When a new listbox is clicked I'd like to remove the highlighting or selection from the last one.
I thought this would do it
".Selected(x)= true" where x would be that listbox's list index. But no.
Is there another way?
View 2 Replies
View Related
Aug 12, 2014
I merged about 15 adresslists from media contacts to one excel list. Each list had a name i.e. music, health, theater, etc. and the same logic in colums. I added a few columns and have 1 large list now.
As some journalists write about music & health & theater, architecture, etc. they are listed up to 10 times in the new list now. But the "genres" from the original list i.e. music, health, theater, etc. are in different columns. Some of the lines have empty fields (i.e. no address or mail)
All I want to do is have one line with all the information of all 10 lines in it, merged, dupes removed:
company - firstname - lastname - Adress - Mail, etc. : genre: music - health - theater:
example.xlsx
I atteched an example of the full list and the result i want
View 9 Replies
View Related
Jul 25, 2014
I am using this code to hide or unhide rows of text on another sheet:
VB:
Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)
Dim sAddress As String
Dim sValue As String
'Get the address of the cell that changed without '$' signs
sAddress = Target.Address(False, False)
[Code]....
When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.
The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..
Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)
Code solution can be entered directly beneath:
VB:
If Target.Value = "Not Pursuing" Then
ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True
View 1 Replies
View Related
May 8, 2013
I have a worksheet that has 8 activex listboxes. Each listbox is tied to the sames list of values (identified as a named range). The named range is a list of countries. Each country should only be selected once, therefore, I would like the selected country(ies) to not show up as a choice when the user makes a selection from another listbox. If this is too hard, maybe we can get a msgbx to appear anytime the users tries to select a country that has already been selected.
View 3 Replies
View Related
Mar 7, 2014
I'm trying to come up with a single formula to create a single column list from a table with blanks.
a
b
c
d
e
f
g
To
a
b
c
d
e
f
g
I know I've done this before but having trouble visualizing today.
View 14 Replies
View Related
Jun 5, 2013
I need to remove values from column A that are present in column C without modifying the order in column B. To be more explicit below is an example:
Column A---------Column B-------Column C
5230400----------81,50----------1660000
3275500----------78,00----------6245700
2856300----------47,50----------5230400
1879800----------62,50----------2497900
2124300----------99,00----------1879800
2497900----------65,00
4826900----------77,50
2124200----------34,00
6245700----------61,50
3324400----------86,00
1660000----------53,00
Column A represents 600 materials.
Column B represents 600 prices. (different for each material)
Column C represents 107 materials that are present in column A and have to be removed along with their prices in column B.
Now i want to remove the materials that are in column A and C along with their prices, so i won't damage the order in the file.Meaning if i delete a row in A and shift the other materials up or down, and in the same time column B remains unchanged, the remaining materials will have different prices...and that's not good.
View 4 Replies
View Related
Jan 17, 2008
I am attempting to Organize some data; however, I have run into a problem. I will paste a sample of the data that I have and explain the situation....
View 9 Replies
View Related
Mar 28, 2005
I need to remove any data which is say 20% above or below the overall observed data, in order to compute simple High, Low and Average excel function calculations.
Is there any way to remove such outliers when calculating averages, lows, highs etc?
View 2 Replies
View Related
Apr 24, 2007
I´working on an excel sheet where i´m copying a range to another place. So far so good. Then I want to remove all double entries for each rows in the new range. I managed to get a code working for one row. When I want to loop it for all the rows in the copied range, I get a an error popoing up when the loop starts working on the second row. Error 457: "This is already associated with an element of this collection" The line creating this error is coll.Add cell.Value, CStr(cell.Value)
Sub Sortere_StederBeta()
Dim coll As New Collection
Dim lcount As Long
Dim cell As Range
On Error Resume Next
Range("B4:U33").Copy
Range("B36").Select
Selection.PasteSpecial Paste:=xlPasteValues
For i = 36 To 65 Step 1
Range(Cells(i, 2), Cells(i, 22)).Select
Set coll = Nothing........................
The problem seems to come from the fact that the Coll (New Collection) is not reseting for the next loop. I tried to set the Coll to Nothing but doesn´t have any effect.
View 5 Replies
View Related
Jan 21, 2010
For each unique value of Order Number [col A] + Line Number [col B], I need a program which will locate the first instance of Next Stat [col D] = 530 and the first instance of col D = 540, deleting the other lines. The output will have two lines remaining for each unique col A+B combination, one where col D = 530 and another = 540. The data will always be sorted in date/time order prior to running this program. The actual data set has a varying number of lines, usually 1000+.
View 4 Replies
View Related
Feb 9, 2012
I have a 5 column document with about 3000 entries that I need streamlining. THe first column is a student's name, then grade. The next 2 columns are for Honour Roll Standing (A or B) and/or an Effort Honour Roll Standing (E). Finally there is also a "term" column indicating if they received this award is Term 1, 2 or 3.
An example source file is found here: [URL]...
As you can see, there are many students who have received an award in all 3 terms, however there are also some students that only received standing for 1 or 2 terms.
I need to manipulate the data so that there are no duplicate names and all the data regarding Honour Roll standings and which term it occured in are all laid out in one row per student.
IDEAL COLUMN HEADINGS:
NAME | Grade | Term1 HonourRoll | Term1 Effort | Term2 HonourRoll | Term2 Effort |
etc etc etc
View 9 Replies
View Related
Jun 29, 2014
Is there a code that can be written to remove duplicates within a cell for an entire column?
View 1 Replies
View Related
May 28, 2012
I am using Excel 2007 on Windows 7. I would like to remove duplicates as follows: If column A = column A, column B = column B, and the two rows are within one hour of each other, then remove all duplicates, leaving one instance of the row. As you can see, sometimes the duplicates are right next to each other, and sometimes have rows between them. (Rows 1 & 2, Rows 30 & 32). Even though this sample doesn't show it, sometimes there can be tens of duplicates within an hour, not just two.
Excel 2007
ABCD1EruptionI Can't Stand The Rain (78)3/7/20129:12:55
AM2EruptionI Can't Stand The Rain (78)3/7/20129:10:02
AM3ChicagoJust You 'N' Me (73)3/7/20129:05:59
[Code] .........
View 2 Replies
View Related
Apr 10, 2009
I have 2 columns: Locations (Column A) & Dates (Column B).
I want to find the most recent date for a selected Location (stored in $G$1).
currently I have in C2: =If($A2=$G$1,$B2,"") copied down the column to the end of data (currently C153). Then in C1: =Max(C$2:C$153).
I also have in D2: =If($C2<$C$1,$C2,"") copied down to D153, and in D1: =Max(D$2:D$153) to find the 2nd most recent date.
Is there some way to condense this into just 2 cells?
See attached file for example (note the columns are currently sorted by date, but that is not always the case).
View 14 Replies
View Related
May 22, 2013
Suppose I have some data in a column like below
Blue
Red
Green
green
red
RED
BLUE
I need a macro or excel formula so that it will remove duplicate products- case sensitive(removing all duplicate prod using using excel,that i know.) i.e. it should not consider Blue BLUE as duplicate. only consider Blue Blue as duplicate.
So the macro should work same as data -> remove duplicate function already exists in excel, except it will be case sensitive..
View 7 Replies
View Related
Dec 8, 2009
In a worksheet, J2:J52 contains a list of unique strings; each cell populated by an embedded formula. Some of the cells contain a valid 'blank' "" given by the formula.
I'd like K2:K52 to contain the values J2:J52, with all the populated cells stacked at the top and the 'blank' cells at the bottom. 'Blank' cells do not have to be in any particular order.
I've been fiddling with the formula kindly provided by WindKnife on the second post in the thread below for the past couple of hours, but to no avail:
http://www.excelforum.com/excel-work...om-a-list.html
Columns A:H in the same worksheet are populated and unavailable for use.
View 8 Replies
View Related
Apr 3, 2013
removing duplicate names. Students were allowed to take a quiz as many times as they wanted. I need to remove the duplicate entry by keeping the highest grade.
Here is the setup of my excel file. Column 1 has surnames, Column 2, has first name, and column 3 has grade.
I can't figure out how to filter them based on first and last name because some students have the same name. with the grade as the criteria
I would need an excel formula not macro
View 9 Replies
View Related
Dec 5, 2009
Ive been having lots of trouble programing a multi column listbox to play ball. On the form there is 1 tree view and two listbox's. The object is to double click a list 1 item and have that go into the list 2.
Connected to the listbox2_dblClick.. Takes the selected list item and adds to columns 1 intergra value. This places a annoyance to me where listbox 2 is not fully populated (you will see ive fully populated the list during my own testing) and a item is selcted if double click on a blank part of the listbox it will increase the int value of the selected item. I though of populating the list with blanks (used "." as visual) so a cheek could be made on the double click item and if blank do nothing.....
View 5 Replies
View Related
Nov 18, 2008
I have a long list of values in column F of my worksheet. These values appear in random rows for example: cell F3 is 27, cell F9 is 7, cell F13 is 27, cell F20 is 9 ... The data is not evenly spaced any specific number of rows apart, but there are spaces (rows which are blank in column F).
I would like to put all of these values in column G but without any blank cells, and keep the order the same as well. So using the above example, Id like a way to make cell G1 read 27, G2 read 7, G3 read 27, G4 read 9.
Column F could be a very long column im not sure exactly how long as the data is being put in periodically. Some sort of formula that I could just drag down that would read the values from column F and put them in column G with no places would be ideal.
View 9 Replies
View Related
Jul 15, 2013
Let's say I have 2 files: Source file and working file. In source file there are some text names in a column that are updated once in a while.
1. I need to create a column in "working file" such that is taking values from column in "source file" even when "source file" is closed.
2. It should take only non empty values, because I need to create a cell with validation list that consists of text names from the column.
Solving attempt: By searching some solutions in forum I found that the first part I can do in the following way: copy column from "source file", select in "working file" a "paste special" option and choose "paste link". It works, but the problem is that it imports all the column: if in "source file" the column consists of words "a" (cell A1), "b" (cell A2) and all other cells in A column are empty - in "working file", after linking, it appears as "a" (cell A1), "b" (cell A2) and all other cells in A column are "0" (zeros) till cell A65536. And I need that in "working file" column after linking will appear as "a" (cell A1), "b" (cell A2) and all other cells will be empty, so by setting one of cells in B column to be a list (by "Data" - "Data validation" - "List" ) - it will consist only from "a" and "b", and not from "a", "b", "0", "0", "0", .... (65534 zeros).
View 2 Replies
View Related
Feb 3, 2012
Some code below which I have inherited, basically its looping through one column of data and removing the text at the beginning before the hyphen eg. '123 - data text' to 'data text'.
HTML Code:
'Remove everything before the hyphen in the activity column
Dim SearchStr
Dim CharOffset As Variant
Range("c7").Select
[Code] ......
View 6 Replies
View Related
May 26, 2014
I am trying to create a straight column list that can take the rows and columns of a table, and list only the nonblank items. The formula I am using only seems to work with one column, not multiple.
Formula:
[Code] .....
View 12 Replies
View Related
Sep 16, 2009
I have a macro which I manually have to change the range in order to run the macro, e.g. Set rngData = Range("B4:I12")
rather than having to change the range for each macro, I was wondering if I could run the macro for the highlighted area. I have tried this, but doesn't seem to work.
Set rngData = Range. CurrentRegion.Select
View 2 Replies
View Related
Oct 5, 2009
I'm currently on Manual Calculation mode. I have formulas in cells A1 to A10 which has not been refreshed. Can I select only range A3 to A6 and recalculate only those cells?
View 5 Replies
View Related
Nov 15, 2009
I need help please. Using a macro, I have selected the range C7:C12. Now I need to keep this selection and expand it 18 columns to the right. This would give me a selection of C7:U12.
The original range changes at various times. Some times it might be C7: C452 etc.
I know it's easy, but I can get the code to keep the selection.
View 6 Replies
View Related
Mar 8, 2012
I have a range (of 2 rows) that are set up as a header row and sub row. I want a macro to insert these above the active row (ie. where the user places the cursor), but when I select and copy the range in the macro, I don't know how to refer back to the 'active row' because that's not active anymore.
I'd also like the cursor then to be placed into one of the cells in the new row, ready for the user to start editing.
View 6 Replies
View Related
Jul 2, 2013
I am looping through a selected range. The code searches through column B and finds any data. If found, it returns the value to a different workbook to column B. When there is no more data, it goes to column C and does the same thing. My problem is, when there is no data in a column it returns a blank. How do i get rid of this blank???
View 1 Replies
View Related
Mar 16, 2007
Count & Sum is one of the most usefull function in the Excel,
I always used this, Is it possible in VBA to create such code that if I Select a range and click on Commandbutton1 automatically one msgbox display with the Count & Sum of the Range Item
eg. If Range A2 to A5 has a number like 50,10,20,10
here if the command button is click automatically one msgbox is display
which showing the
-Total Sum of the Seletced Range is 100 and Total Count is 4
View 9 Replies
View Related
