To select multiple columns I can use Union but can I use something that will cicle through the values from an array?
Example (how do I see this)
VB :
columnsToDelete= array("UM","Post","Pre")
For i=0 To UBound(columnsToDelete)
Set columnsFound=Range("1:1").find(what:=columnsToDelete(i), lookin:=xlvalues,lookat:=xlwhole, matchcase:=True)
columnsFound.select
Selection delete
Next i
I have VBA code below. Currently the code allows me in column "N" to choose more than one item in a dropdown menu. I am trying to find a way to make column K and M do the same thing. I have tried a number of ways but to no avail.
View 2 Replies View RelatedI am using VBA to change the format of columns. The code work with 1 column, but In need to change the format of mulitiple columns. E.g Cols 11 to 22. I tried the code below but it does not work!
View 3 Replies View RelatedI am using ADO to select data from a CSV file, column 1 has some duplicate Incident numbers, but the rest of the columns the data may be different but not interested in that so I cannot use SELECT DISTINCT incident, desciption as this applies to all columns
so I tried the following
StrSQL = "SELECT DT.IncidentNumber, ST.Description -
FROM (SELECT DISTINCT IncidentNumber FROM " & StrFile & " ) DT -
INNER JOIN " & StrFile & " ST ON DT.IncidentNumber = ST.IncidentNumber"
however it returns ALL rows, if I use just
SELECT DISTINCT IncidentNumber FROM " & StrFile
I get the rows I am interested in but not the extra data I require
I'm trying to write some code to select cells in a number of columns on a variable row. I have the following code to define the rownumbers for Cash and Pal:
Columns("G:G").Select
Selection.Find(What:="Total Cash", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
[Code]....
Then I can use the following code to select a cell in a single column:
Range("P" & Cash & ":P" & Pal).Select
but I need to select cells in columns P, R, T, V, X, etc.....
If I was doing this for a fixed rownumber I would use:
Range("P22:P23,R22:R23,T22:T23,V22:V23,X22:X23").Select
but because my rownumber is variable I tried to use:
Range("P" & Cash & ":P" & Pal, "R" & Cash & ":R" & Pal, "T" & Cash & ":T" & Pal, "V" & Cash & ":V" & Pal, "X" & Cash & ":X" & Pal).Select
but this doesn't work giving the Compile error: Wrong number of arguments or invalid property assignments
I have a spreadsheet with a heap of raw data in it. I need to perform a search based on a customer name, then return certain columns from the raw data IF the customer name is matched in one column, AND a special flag name is matched in a second column. The customer name is always a single entry for the purpose of the query, however the special flag must be checked against a possible list of values in a table. I know this is hard to explain, and I can't add attachments to my posts, so I have created a sample document and placed in in dropbox:
[URL] ........
I would like a macro that select all cells based on multiple criteria. It's a big sheet, but I've made it smaller in the following example.
In row 1, there are 2 headers, in B1 (electricity) and C1 (gas) In column A, there are 3 houses, in A2, A6 and A10. The blocks of cells in between (e.g. B2:B5) have 2 headers, 'period' in B2, and 'account number' in B4. B3 and B5 have a list of options in them.
I want to select cells that meet certain criteria. Either electricity or gas, and either period or account number. So, if in cell D1 I choose electricity and in D2 period, I would like to select B3, B7 and B11. If I choose gas and account number, I wish to select C5, C9 and C13.
I want to select a number of columns. I know the column indexes.
I want to use something like (Doesn't work):
[Code] ......
Instead of (Works):
[Code] ....
Rows can be selected according to:
[Code] ....
I have written this code that works. But there must be an easier way:
[Code] ....
I'm in the midst of preparing a Year-To-Date (YTD) simple financial report in MS Excel.
Firstly:
I allow user to enter a number in cell A2.
Secondly:
Cells from A4 - L4 are linked from Sheet1. Cell M4 will give sum of A4:L4. Users are not allowed to change data in cells from A4 - L4. Users are only required to enter a number between 1 to 12 in cell A2. Upon update cell A2, the VBA shall sum the number of columns as entered in cell A2.
For example, if user enter 3 in cell A2 then cell M4 must give sum of A4:C4. If user enter 8 in cell A2 then cell M4 must give sum of A4:H4.
an equivalent to the code
rows("8:10").select
for columns. I don't want to use the A, B notation but rather, column numbers.
I plucked a macro off an old post from here (as I always do), but I'm having trouble tweaking it.
VB:
Sub completedtest()
Dim c As Range
Dim rngG As Range
For Each c In Intersect(ActiveSheet.UsedRange, Columns("i"))
[Code] ....
As it currently stands, the macro selects the entire row of rows containing the letter Y in column I. All I need to change is, I want to simply select columns B through I, rather than the entire row.
I have a large spreadsheet of species activity data and want to separate information from two columns to create a bar chart of when a certain species is active over a 24-hour period. There are duplicates of each species in one column and a unique corresponding time of activity for that species in another, something like the following:
Species A 23:01
Species B 07:09
Species C 13:45
Species D 08:30
Species A 01:55
Species A 22:07
Species C 15:29
What formula can I enter that only selects Species A and their corresponding times that I can copy into a new spreadsheet?
I would like to somehow select and print the used columns in my pivot table.
I currently have a code that prints TableRange2 but this also includes blank pivot tables columns which are not in use.
Please see attached.
What I would like to do is for each column within range("a1").currentregion that has dates, select the blanks in those columns and put the date 1/1/1900.
The result is in the Result sheet.
I know how to do a standard loop through columns but I'm not sure how to do this for just the current region and just for those columns with dates.
I need to select all data in columns A:F but the number of row containing data is variable. I need to select only the rows with data.
View 7 Replies View RelatedMy issue is I want to use a for loop to go through a spreadsheet. Within the for loop if a certain condition is true I want it to select all columns from A to AW and merge them together.
Example of what I have so far:
Sub MergeHeaders
dim i as Intger, LastRow as Integer
LastRow = Cells(Rows.Count, "CP").End(xlUp).Row
For i = 1 to LastRow
'Check if the leading character is a letter, not number
If Asc(Cells(i, "AP")) > 64 Then
'I want select from A to AW only for current row i, then merge
end If
next i
end sub
I have an input box which asks the user to select a row that they wish to delete.
What I need to do is take the row number that has been selected and delete cells B:K and move the below cells up.
I am currently setting up a userform where one of the options is for an error code (chosen from a listbox) but the user has to be able to select multiple codes. I have the below working if it is NOT multi select, works a dream and goes in the right column.
VB:
If ListBox2.Text = "A1" Then
LastRow.Offset(1, 20).Value = "x"
ElseIf ListBox2.Text = "A2" Then
LastRow.Offset(1, 21).Value = "x"
But as soon as I set it to mutliselect and choose say A1 and A2 neither one writes, I imagine because Listbox2.text is now A1,A2 and not just one or the other.
I am working on a =transpose function. Pretty simple right? Well now I am trying to have it SKIP/SELECT certain columns.
I have =transpose("date!B1:T1"), but now I want to "Select A,B,D,E,F"
=TRANSPOSE(INDIRECT("date!B1:T1"),"select A,B,D,E,F,G,H,P,Q,R,S,T")
I am trying to find a formula that will look at the figures in four columns and then select the lowest for the calculation.
Basically what I am doing is getting venders prices and placing them in the column. The cheapest is then selected.
Showing which vender is used would be a bonus.
I have below a code that is selecting from A11 down as far as there is data and across a certain number of columns then sets a print area.
Currently the columns go A, B, JW, JX, JY, JZ, KA, KB as C through to JV are hidden. I have tried to offset the columns by 7 to see if it will select A-KB but it is selecting cells that are hidden and only selecting A-G. I need it to go past the hidden columns. Here is the code:
[Code] ......
Could I also get a code to go from A10 and move over to KB and create a range from there e.g. KB-KJ and last row from there?
I need a macro to start at cell A11 and select across 10 columns and down to the Lastrow (last row with any data).
There are hidden columns after column D so it CANNOT have any columns hardcoded except for column A.
This selection then needs to be set as a single print area to print out on 1 page. I have already set Rows 1-10 as a title area to print at the top of each page.
Code i currently have is:
[Code] .......
I just need it to now go across 10 visible columns and set as a single print area.
so I have been googling to figure out how to write a macro that will select every other column on my workbook. All that keeps coming up is conditional formatting, which I do not want. I am not trying to highlight the columns in alternating colors, I just want to select them.
I am starting at column F and I would like it to search until the end of the columns and select every other column ending with the last column with data in it.
I know I could search by a specific row and tell it to find the first blank cell then offset it, but there is no particular row that will always have data in it, so I guess it needs to check by column until it finds the first column without any data in all rows and then offset it.
Anyone know what I am talking about here?
I know it should be fairly simple but I couldn't seem to find any answers anywhere else.
Also, anyone know any good websites that have definitions for some basic macros? I'd love to start learning how to write this stuff on my own.
I have a macro that allows me to select a range of columns and unhide any that are hidden. I am on the sheet "Employees", but the action happens on the sheet "Enter". I can run this macro when I am on "Employees" and jumps top "Enter" and works exactly as I want it to.
Code:
Sheets("Enter").Select
Columns("B:T").Select
Selection.EntireColumn.Hidden = False
Range("M8") = Sheets("Employees").Range("B5")
Range("I7").Select
But when I try to run it as part of another macro that begins on "Employees", I get "Run-time error '1004': Select method of Range class failed", and Columns("B:T") is highlighted.
I have a large spreadsheet with over 50 columns but usually only need to view a small selection of them. Many colleagues use the sheet and often use the format, hide, column function to hide columns they do not need but then the next user has to unhide them and re-hide others until they are left with the different columns they require.
Is there anyway of creating a menu which allows users to select from a list of all the columns those they wish to display, all the others therefore being hidden.
by what means is it possible to unselect randomly selected areas, rows, columns from all sheets?
View 4 Replies View RelatedI have a workbook that is connected to a MySQL DB.
I want to get all the months organised in columns then the Amounts of those months underneath the specific month. I can only do one at the moment in this select query but do not know how to go forward by creating the other month and their data?
My code looks like this:
Private Sub cmdGO_Click()
Dim conn As ADODB.Connection
Dim dbRecset As ADODB.Recordset
Dim sSQL As String
Dim l As Long, l2 As Long
Dim Last_Row As Long
I would like to take the data from worksheet1 and put into worksheet2 but limit the length of a list (the real spread sheet has over 100 rows and i would like them in 4 sets of 25 versus the example I provided). Is there an array or macro that would make this work (keeping the formatting)..
Excel 2007
B
C
3
Name
Company
[Code].....
I have a need to combine multiple columns into a long list of results in 2 final columns. Here is my example...
Current layout with varying number of data points per row...
ColumnA | ColumnB | ColumnC | ColumnD | etc...
example1 | Test1 | Test2 | Test3 | Test4
example2 | Option1 | Option2
example3 | Number1 | Number2 | Number3 | Number4 | Number5
Desired Result
ColumnA | ColumnB
example1 | Test1
example1 | Test2
example1 | Test3
example1 | Test4
example2 | Option1
example2 | Option2
example3 | Number1
example3 | Number2
and so on and on and on until all rows have been filled in down the file...
Is this possible?
Function to select two columns based on the header and the variable given.
Sample excel file attached for your ref.
Excel sample.JPG
Find the excel file in which I am looking for an formula which will look up variable in Col A for Eg USD and search the same in Row 1 and then will select COL D:E and so forth for other currency.