I'm having to copy and paste variable amounts (rows) of data into a single column. Thus, I need to know which is the last row that has data in it. I'm using the following function to get me this information: - Public Function LastRow(mySheet As String, myCol As String) As Long
-
Dim ws As Worksheet
-
Dim lRow As Long
-
-
Set ws = ThisWorkbook.Sheets(mySheet)
-
-
With ws
-
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
-
LastRow = .Cells.Find(What:="*", _
-
After:=.Range(myCol & "1"), _
-
Lookat:=xlPart, _
-
LookIn:=xlFormulas, _
-
SearchOrder:=xlByRows, _
-
SearchDirection:=xlPrevious, _
-
MatchCase:=False).Row
-
Else
-
LastRow = 1
-
End If
-
End With
-
-
End Function
Lets say that in column A, I have 48 rows of data (percentages) and in column B I have 16 rows of data (numbers). Column C also has 48 rows of data (again numbers). They are all on sheet "NewSheet". Here are the results that I get when I call this function in the immediate window for each row: - ?LastRow("NewSheet", "A")
-
48
-
?LastRow("NewSheet", "B")
-
1
-
?LastRow("NewSheet", "C")
-
1
Why does this work for column A, but not for columns B or C?
8 2156 NeoPa 32,556
Expert Mod 16PB
Are you trying to count the number of cells in the column or find the highest row number Seth.
Your code doesn't specify the required column anyway. It simply uses .Cells, which isn't what's required.
I just copied the code from the internet and then modified it so that I could pass it the sheet and column names. I really don't understand the code, so I'm not sure what is and isn't required.
As for what I'm trying to do... I need to find the last row that has data in it. So if rows 1-16 have data in column B, then I want it to return 16. I suppose a count of the rows with data would also work as there shouldn't ever be any gaps in the data. After re-reading your question, I think that the answer would be to find the highest row number.
I thought I was specifying the required column by passing the A, B or C to myCol which is used in line 10 of the the first code group. However, like I said before, I don't understand this code so I'm not sure exactly what I'm doing. This is probably on the second time I have touched Excel VBA.
I did find the following code on mrexcel.com - LastRowColA = Range("A65536").End(xlUp).Row
which I modified to - LastRow = Range(myCol & "65536").End(xlUp).Row
and placed it in line 20 of my OP. However, I'm not sure if this is a good method as it comes with the following note: "but this doesn't tell you FOR SURE the last used row in the entire sheet, unless you can be certain that Column A holds the data." I think that I have fixed the problem by passing it the myCol, but I don't know. I also assume that this only works for the active sheet. At this point, I think that this is okay, but I might need to be able to find the last row for a different sheet as I continue with my project.
Here is the link where I found the code: http://www.mrexcel.com/td0058.html
NeoPa 32,556
Expert Mod 16PB - Range().SpecialCells(xlLastCell)
This will give you the bottom-right cell used or referenced within your ActiveSheet. The Range() parameters are immaterial. Earlier versions of Excel needed a save to occur before this was ever moved towards A1, though extending the area would always work.
Can I specify a range? I need the last row for a given column, not the whole worksheet.
NeoPa 32,556
Expert Mod 16PB
No. That suggestion was for when the opposite is true. Your modified code in post #4 should give you exactly what you need for that. It wasn't very clear, but I got the impression you wanted something different from that from post #4.
Okay. I'll just use code then to change the active sheet to the one that I need and not worry about specifying a sheet. Thanks NeoPa.
Luuk 1,047
Expert 1GB -
Public Function LastRow(mySheet As String, myCol As String) As Long
-
-
LastRow = ThisWorkbook.Sheets(mySheet).Range(myCol + "1").End(xlDown).Row
-
End Function
-
I dont know why i have 'myCol + "1" ', and in your code i read ' myCol & "1" ', but maybe its just because it's Excel2013
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Astra |
last post by:
Hi All
I know an SQL Server timestamp seems to be as useful as rocking horse for
show jumping, but I'm hoping you know a 'fudge' to get me round a problem or
at least confirm that it isn't...
|
by: UDBDBA |
last post by:
Hi:
we have column with GENERATED ALWAYS AS DEFAULT. So, we can insert into
this column manually and also let db2 generate a value for this column.
Given a scenario, how can i find the NEXTVAL...
|
by: User 2084 |
last post by:
Hello all. I'm an access newbie trying to learn how to do basic
database data manipulations. I had a hard time searching the archives
on this question as I don't really know what I'm looking for in...
|
by: Amalorpavanathan Yagulasamy(AMAL) |
last post by:
Hi All
How can change color of the Sort icon in Data Grid column Header
Regards
AMAL
|
by: jrett |
last post by:
I'm new to ASP.NET and fairly inexperienced with web development in general,
but I've been a professional software dev for over 10 years, C++, Unix and
windows, C# the past 4 years.
I've been...
|
by: Ryan Liu |
last post by:
Hi,
Is there a way to specify data Grid column Style if its DataSource is an
IList(ArrayList), not a datatable? Can I selectively only show few columns,
not all of its properties?
Thanks!
|
by: andreas.hei |
last post by:
How can I get the value of the last Item in a coums?
SELECT COLUMN FROM DATABASE ORDER BY COLUMN DESC?
something with Fields(0).value?
Thank you in advance
|
by: ramana |
last post by:
I'm wondering if someone could point me to the flaw in the following
code that uses the while(!FP.eof()) condition to read the input data.
This condition is reading the last data point of the file...
|
by: Dayah |
last post by:
Hi,
Can someone provide me the c program to read the data in Excel file. Let say my file name is book.xls and there are 3 data in column A which are 2.400E1, 2.500E1 and 2.600E1. I tried my own...
|
by: smophie |
last post by:
Hi, I'm new to VBA and I'm trying to automate a copy/pasting of a column.
Is there anyway to write a code that finds the last column that has data, then select it and paste it in the next column?...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| | |