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 2163 NeoPa 32,564
Recognized Expert Moderator MVP
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,564
Recognized Expert Moderator MVP - 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,564
Recognized Expert Moderator MVP
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
Recognized Expert Top Contributor -
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 possible.
I have 2 tables, one called ACCOUNTS and one called STOCK.
These tables have the usual ints, varchars, etc and have a timestamp field
as...
|
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 for this identity column?
We can do "VALUED NEXTVAL FOR SEQUENCE", but for identity how can it be
done?
|
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 this
case, so please refer me to previous posts that address my question.
Thanks in advance.
My question relates to finding complimentary data...
|
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 working on a personal ASP.NET project, basically as a learning
tool and I have a major issue which is really really getting on my nerves.
I'm...
| |
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 twice.
#include <iostream>
#include <fstream>
using namespace std;
int main(int argc, char **argv)
{
double x;
|
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 program but it didn't work. Thanks.
Regards,
Dayah
|
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?
Thanks
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| |
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| | |