Range Of Cells Of Known Columns But Unknown Rows
Mar 19, 2008
I'm working to build a macro to help clean up data I download out of our company's online resources. I know that the data will run from column A to N but the number of items (and thus number of rows)will change. I need to sort all used rows except for the last one (which containes totals and I don't want to include in my sort). So right now I'm just trying to figure out how to select from A3:NX, where x is the row above the last used row.
What I've managed to find so far on the forums is
Offset("$A$3", 0, 0, (Match(9.99999999999999E+307, "N:N", 1) - 1), 14).Select
which I think should find the last row in N to have a number in it, minus 1. However I'm getting a compile error that function or sub are not defined. (seemingly refering to the Match, but I'm not sure).
View 6 Replies
ADVERTISEMENT
Sep 9, 2006
I need to make named ranges from an unknown number of columns(at least 1) each with an unknown number of rows. Each column has the name of the named range as the first row, and then a variable number of rows containing part numbers.
I can do it 1 by 1, but id rather do it in a loop so that blanks dont cause errors. there will be different people using versions of this sheet with different model/part number information What i've tried: Count number of colums with row 1 containing data (11 max, which is more than will ever be used) add into array(I know i dont really need to add into the array, but i might use it later for some other code). The problem i'm having is finding the range of rows that need added to the named dynamic range and adding it.
modelcount = Range("G7") 'G7 (for now) contains =COUNTA(H1,I1,J1,etc)
For i = 1 To modelcount
Redim Preserve Models(0 To i)
Models(i) = Cells(1, i + 7)
Range1 = Cells(2, i + 7).Address(xlA1)
lastRow = Cells(rows.Count, i + 7).End(xlUp).Row
Range2 = Cells(lastRow, i + 7).Address(xlA1)
Reference = Cells(2, i + 7).Address(xlA1)
ThisWorkbook.Names.Add Name:=Models(i), _
RefersTo:="=OFFSET(Reference,0,0,counta(Range1:Range2),1)", Visible:=True
Next i
This gets me the range i need, but doesnt create the named range properly. If i go to insert>names>define, the named ranges are created, but they dont relate to the data in any columns. It shows the variable names rather than the cell range the variable represents.
View 2 Replies
View Related
Oct 26, 2009
I've been working on a spreadsheet and these forums have been a great help. I'm now at the very last section and, surprise surprise, it's also the hardest!
I'm creating a stock trade recording sheet. I have a userform ask the user to enter a date, a time, the number of stock purchased, and the price of the stock. These are then entered in a new row.
Now what I want to do is have summary cells which say how many stocks were purchased and the total profit made for each day. Since each time is given its own row, I can't know in advance which rows to sum over. I also don't know on which days a trade was made. So a summary cell should only exist if a trade was made that day.
Could I do something like.... check if the date matches then sum over all the values for that date? So if column A has the dates, can I say "Search which rows in column A have this date" then "for those rows, sum column C"?
Also, how would I create a a row for each traded date's summary cell and enter the date in it? I've attached a spreadsheets which manually demonstrates what I want to do (no macros) and a spreadsheet with what I have so far (basic macros).
Any and all help much appreciated, I just need to get my head around creating and dealing with variable ranges. Is that a really advanced task? I don't think this is a one line solution so please bear with me while I make mistakes!
View 14 Replies
View Related
Jul 25, 2007
i have a list of countries in a column and have to go through this column ensuring they are in the correct format. I'm struggling to figure out how to iterate through each row of the column, test it's value and then change if necessary...
for example
column a --> column A(modified)
Usa --> USA
Ukraine - Mobile (Umc) --> Ukraine - Mobile (UMC)
Uk --> UK
Uruguay --> Uruguay
so values in column a becomes like those shown above.
View 9 Replies
View Related
Aug 25, 2006
How do I define a " Range Name" that is fixed? So if I insert a row above the range it has no effect on the orginal range.
IE: Range Name "MyRange" = A10:A100
I insert a new row at A4 then "MyRange" = A11:A101
I want it to stay at A10:A100.
View 5 Replies
View Related
Jun 16, 2009
I created a macro on an Excel spreadsheet:
Sub Macro1()
Range("C11:D19").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
Range("E5:E20").Select
Selection.NumberFormat = "0.00%"
Range("D24:E43").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
ActiveWindow.SmallScroll Down:=24
Range("D58:E339").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
ActiveWindow.SmallScroll Down:=57
End Sub
When the macro is run, it works with the exact amount of data that I've entered.
However, because I have specified a specific range, if I add a row or column of data to the spreadsheet and run the macro again, it throws the whole thing off (certain cells get formatted when they shouldn't and others aren't formatted)
How do I tell the macro to look in the spreadsheet for varying ranges of data and format those cells?
View 9 Replies
View Related
May 25, 2008
I am trying to average a range which continually changes depending on the amount of registries entered in a given week. for example, one week there may be 5 registries which would fill five columns while another week there may be 15 registries, once again filling 15 columns. I would like to find the average of x amount of columns.
My initial approach was to select the first blank cell to the right of the data and then find the last filled cell (which should be the first column of data.
with this range i tried hopelessly to use the average function (did not work):
Sub averagemake()
Dim iLastColumn As Integer
Dim Rng As Range
Sheets("All Data(Values)").Select
Range("A6").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
iLastColumn = Cells(6, Columns.Count).End(xlToLeft).Column
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-iLastColumn]:RC[-1])"
End Sub
View 5 Replies
View Related
Apr 24, 2006
Need to sum through an unknown number of row generated by advanced filter at run-time
Formula at C16
Data starts at row 57 (from copy/paste - advance filter)
Last row unknown
Criteria to match in A16
Data to be evaluated in B57:B (row unknown)
Found this formula on microsoft.public.excel.misc
=SUM(A57:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))
Which I modified formy start row
View 9 Replies
View Related
Feb 18, 2010
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.
View 7 Replies
View Related
Jul 15, 2009
I need to know the function to copy an unknown amount of rows. After a certain row it should copy everything.
So like after row A6 it copys every row below it with data in them then I want to paste it in another worksheet. I dont want to just do
Rows("6:65000").Select
Selection.Copy
If someone could point me to a good site that explains alot of useful VBA commands that would help me alot. Most of my problems come from just not knowing the full function names.
View 10 Replies
View Related
Sep 15, 2012
I need to put a value on every line having "file:" in the second column. The value requested is a sum of the numbers in the forth column following this line until the next "file:" line.
I know it is not trivial, but sure it is possible.
57
file:
HIRES-~1
#VALUE!
58
208
1
1
[Code] ...........
View 9 Replies
View Related
Sep 30, 2009
I am working with 2 workbooks the first one "LH Enrollment" is used to enter information for new students being enrolled in the program. After the information is entered the macro is then copying the information to the second workbook "LH Children Records". The part I'm stuck on is that I now need to sort alphabetically by last name which is entered on Sheet 1, Column B of "LH Children Records". The number of columns will stay the same but the number of rows will change each time a new child is added. Eventually all this information gets copied over to several other workbooks, but I'm stuck trying to sort. I have attached both workbooks
View 5 Replies
View Related
Dec 16, 2009
I am trying to find a way to combine two worksheets with identical columns and an unknown number of rows. Both sheets use columns A through K.
I want to add a new sheet called "Combined orders", then copy data and headers from Sheet1, paste it to Combined orders, copy data only from Sheet2, and paste it on the row after the last row of data from Sheet1.
Sounds easy but I am easily confused by the unknown number of rows in each data set. Can someone please help?
Also, are there a few lines of code that I can copy and paste into new macros that will make the unknown rows problem easier for me in the future? (i.e., instead of just showing me the code, can you also please explain the concept behind it so I can learn for future reference?)
View 7 Replies
View Related
Jan 24, 2007
In the code below, a formula is placed in column F to compute the total of that column. I first find the number of rows and place the formula in the cell below it. I'm dividing the sum by 2 since there are subtotals in the column.
Sub AddColumn()
Dim NumRows As Long
NumRows = Range("A65536").End(xlUp).Row 'get the row count
NumRows = NumRows + 1
Worksheets("Report").Cells(NumRows, "F").Value = "=SUM(F9:F308) / 2"
End Sub
The problem with the code is that I don't really know that the last row in the column is F308. I need to replace that part with a variable. It will be something like this (which I know is incorrect):
Worksheets("Report").Cells(NumRows, "F").Value = "=SUM(F9:NumRows) / 2"
How would I use a variable in the SUM function in place of the cell names? Should I even be placing the formula in a cell, or should I compute the sum in the macro and place the value in the cell?
View 2 Replies
View Related
Aug 13, 2006
is it possible to creaate a macro to highlight a range that starts at a1 to a? and there could be blanks amongst that range. the range will be unknown
View 9 Replies
View Related
Mar 11, 2007
I'm trying to set a range to a particular cell in Col D but I don't know the row number because this can vary depending on the amount of data entered. At present my code looks like this
Dim MyCount As Integer
Dim r1 As Range
'No of rows in Col D
MyCount = Range("A2:D2", Range("A2:D2").End(xlDown)).Rows.Count
Set r1 = Range(Cells(MyCount, 4))
The problem is with my line
Set r1 = Range(Cells(MyCount, 4))
View 2 Replies
View Related
Jun 16, 2009
I need to autofill a range of formulas (A2:O2) but the actual range to fill up depends on the range of data on sheet1. I've managed to use:
View 4 Replies
View Related
Dec 15, 2009
I have a set of data in column R, with an unknown number of rows that looks like this
Days Late
-28
150
3
16
41
.
.
.
and I have written a script to add an adjacent column "S". I want to fill column S with conditional values based on the value of column R, sorted into categories such as "On Time", "Less than One Month Late", etc. Here is what I have so far, it doesn't work:
View 4 Replies
View Related
Nov 4, 2009
I have the following setup,
Down column A I have a list of jobs, job 1 , job 2 etc
Down column b I have either a blank cell or other data (will be numbers but is irrelevant)
What I want is a formula for a cell that tells me which titles in column A have any data at all in column b. I need it to look like the following : Job 1, Job 2, Job 4
assuming b1 b2 and b4 have any contents and b3 and b5 and onwards are empty
I am seeing that concentate gives me close to what I want but the number of rows in column a and b change constantly and also may be above 30
View 9 Replies
View Related
Apr 26, 2007
For example
Name Address Phone # zip
Danielle 4561
Danielle 9852
Danielle 22
Danielle 69
Joe 895
Joe 28
John 9821
John 1114
John 698
Say I did a search for Joe. I want to report back all the addresses in which he resided but there's no way to tell how many rows of data each person has. Joe has 2 rows, Danielle has 4 rows and John has three. How do I report back all the relevant rows?
View 11 Replies
View Related
Jul 21, 2009
i have a sheet for monitoring sickness. i have used a macro before which automatically selects a range and emails it to recipients when i press a button. what i would like to do is have excel automatically choose the range for me based on today's day. in the attached example i would like column "A" copied along with the 5 columns before today's date, today's date column and also the 5 columns after today's date. paste the lot into an email and send. (i have highlghted the parts i would like copied and pasted into an email based on today's date (21st july).
View 4 Replies
View Related
Oct 24, 2012
Using the following code to remove empty rows based on whether a specific range of columns is empty. The code works if the cell has a zero, but not when the cell is blank. An example of the data is attached.
VB:
Public Sub DelRows2()
Dim Cel As Range, searchStr, FirstCell As String
Dim searchRange As Range, DeleteRange As Range
[Code].....
View 1 Replies
View Related
Mar 6, 2014
I am using the following range to copy data from Column A (from rows 3 on the the last row). How can I include Columns B and C into this range also, but still using Column A to find the last row?
[Code] .....
View 9 Replies
View Related
Dec 7, 2006
create a Dynamic Named Range for Pivot Tables
the pivote table takes data from A1 to Kx
x is the last row of column K that is not 'blank' ( whenever I insert a new row),
I already know how to define a zone but not know how to to make it "dynamic"
View 5 Replies
View Related
Jul 5, 2007
I have named ranges and I want to get the total number of columns/rows or the last cell so I can index relative off off it to get at a value of an individual cell. No I dont want to go definning all names for this specific location, I want the flexibility to relative address it from the row/column or last cell of the named range. I looked at the column and columns props. The first gives a number that makes no sense and not a total of all columns and the last gives back a Range of columns, wonderfully recursive.
View 7 Replies
View Related
Jun 23, 2013
I would like to modify my code below to count columns and rows from a different range. Currently, the code below only reads from A1 of my data table. My data table starts at F7 with n columns and y rows. Each time I run the macro it does not recognise the range but only from A1.
How to modify this code to read for a defined range?
VB:
Dim iColumnCount As Integer
Dim lRowCount As Long
Dim iCol As Integer
Dim dAverage As Double
[Code] ......
View 4 Replies
View Related
Jan 5, 2009
I am still trying to develop a macro that will copy and paste a set of formulas into a dynamic destination range. I have included a worksheet that shows what I am trying to do. Basically when using AutoFill I need the columns of the destination range to be dynamic based on the number of rows in another sheet.
View 13 Replies
View Related
Aug 10, 2006
I have a Sheet which gathers data from another Sheet in the same Workbook, then uses that data to produce more output. However at it's smallest it is only 2 rows of data deep and 2 columns of data wide, and at it's greatest it is 100 rows deep by 48 columns wide.
I would like to display only the relevant output by "Unhiding" the completed rows and columns.
After headings etc the rows start at 28 and the columns at N.
I have written the following which works for the rows but nothing happens with the columns,
Private Sub Worksheet_Activate()
Dim MyRange As Range
Dim MyRow As Integer
Set MyRange = Range("a28")
MyRange.Select
Do
MyRow = MyRange.Row
If MyRange.Value > 0 Then
Rows(MyRow).Select
Selection.EntireRow.Hidden = False
End If
Set MyRange = MyRange.Offset(1, 0)
Loop Until MyRow = 128
End Sub...
View 3 Replies
View Related
Oct 20, 2006
I posted this on Mr Excel, but did not get an answer. I hope someone here can help.
I have a rather large financial worksheet that I am trying to extract some specific data from.
The sheet contains budget and expense #'s for the fiscal year for several projects. The project names are listed in column G and the same project can be on multiple lines, depending on what the expense is. Column J contains either "Cap" for Capital, or "Exp" for Expense. The monthly data starts at Row L. Each month has 6 columns, the one that matters for this scenereo is the 5th one for each month, which is the Forecast Column.
Our Fiscal Year is April 1 - March 31, but this sheet ruins from March - March so that we can carry over anything from the previous year. So the forecast column for March is column "P", April is column "V" and so on, adding 6 columns for each month.......
With me so far?
What I am trying to do is get the total forecasted expenses amount for a specific project for the remainder of the fiscal year.
In other words I need to look for a specific Project Code in Row G, look for "Exp" in Row J, Then get only the numbers that are in the Forecasted Column for months in the future and add them all up.
Just in case that is not totaly clear----
criteria to meet
Column G = "Project Code"
Column J = "Exp"
Row 2 > this month
Row 3 = Forecast.......................
View 9 Replies
View Related
Mar 19, 2008
Im writing a macro and have a cell activated (using ActiveCell). If I want to select this cell (the activated one - i.e., k3) along with the 2 cells next to it (i.e., l3, and m3), how would i go about doing that?
I'm wanting to shift 3 cells downward and I know how to shift them down, but dont know how to select the cells i want.
View 5 Replies
View Related
