Free Microsoft Excel 2013 Quick Reference

how to know or check the last row in the worksheet using vba


I had a sheet with many columns. i want to select the cells starting from row one to the last row in the worksheet, then copy it and paste it in other sheet.

1. How do i know which cell is the last one in the sheet
2. how to select the rows or cells when i use the auto filter in the worksheet and select certain Cells like PCs in my example.
i need to select all of them,copy the whole row containing that particular word and paste it in other sheet.

My data keeps changing every day in the above scenario. I tried using the UsedRange Property

How do i solve this.

Post your answer or comment

comments powered by Disqus
If you aren't interested in context, but think you can help me, please skip to the boldized section below for the abridged version of the problem!

I have a worksheet with a button that updates a second sheet with a current list of what we have in stock (so we can print out the second sheet and use it to manually take inventory). So, it copies a range named "Inventory," on the first sheet, that has a row count that fluctuates with the number of different commodities we actually have in stock at that given moment and then pastes that copied selection into the second sheet, over top of whatever old inventory was left over from the last time we pushed the update button.

What I'm trying to do is add a signature box (for inventory-taking accountability) directly below the pasted area after I paste it. Now, of course, the copied and pasted area could have 5 rows or it could have 15, and when I copy it from the first sheet and paste it into the second it doesn't preserve the name of the range on the second page, so what I really need to do to add the signature box directly below the last row in the pasted area is specify the location in relation to the last row in the pasted selection, but I don't know how to do that. Using ActiveCell always refers to the top-left-most cell in the selected area. So...

If I have an area selected, how do I refer to the bottom-right-most cell in that selection (in the same way that ActiveCell refers to the top-left-most cell)? Or at least the bottom row? Or, even better, the row below the bottom row?

ps. I can't upload an example sheet due to a nondisclosure agreement I've signed.


I have a workbook with multiple sheets that need to be combined into one. There's a referral date in column A, and first and last name in columns C and D, respectively. I have a macro that combines the multiple sheets that works great, or it would, if everyone filled out the date that goes in column A. But they don't. They do, however, fill out every name in Column D. How can I get the macro to find the last row in Column D, but copy the data in starting at column A, even if a cell in column A is blank? My complete code for the loop is below, and this in particular is the line I need to change:

Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)

For J = 2 To 3
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
End If
Next i
Selection.Offset(0, 0).Resize(Selection.Rows.Count - 2).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)

Thanks in advance,


I have a macro that formats the worksheet from a text file to an excel file.
I insert a column and create a formula on the second row of that column.
How do I determine the last row in the worksheet so I will paste my
formula until the last row on the new column?

Thanks in advance.

I want to select and copy a range of variable size. I know the the first row of the range is from "G3:M3" However, I don't know how many rows deep the range will be. Sometimes the Range may be ("G3:M27") or ("G3:M15").

Additionally, I want to select everything EXCEPT the last row in the range. Also, there is other data below the first range of data that I don't want, so using Range("G65536").End(xlUp) won't work either.

I used the following code and I keep getting Error '91' Object variable or With block variable not set.

I think it's close to doing what I want it to do?!

Sub lastrow()

Dim lastrow As Range
lastrow = Range("g3:m3").End(xlDown).Row - 1
Range("g3" & lastrow).Select

End Sub


Hello everyone

Does any body know how to change or remove the TitlebarIcon from an
Excel Workbook.
In MSAccess you can do that in the menu> options>startup
In Excel, as far as I know, there's no feuture like the one in Access.
I do know how to change the Titlebar Caption.

Thanks in advange,


Teuntje's Profile:
View this thread:

I am quite new to Excel VBA. Have a spreadsheet with buttons located in the first 5 rows. Data is to be located starting from row 6. Basically I have created a form which is opened by one of the aforementioned buttons. The form accepts user input data. The user then clicks on a button within this form to write the data to the spreadsheet. How can I write some VBA to write the data to row 6 in the first instance and then the following rows thereafter i.e. row 7, row 8 etc allowing for the fact that when the user closes and then re-opens the spreadsheet the VBA will have to know which is the last row containing data so that no data in the existing rows is overwritten? Tricky eh? Have tried numerous bits of code but nothing seems to do the job. Someone please help!!!

I want to find the last row in within the range Z22 to AB52. However an error occur if all the cells within the range are empty. I tried to add in "On Error Resume Next" but the lastrow value did not return a 0.

How can i assign lastrow value to be 0 if all the cells are empty?
Thanks for any help in advance!

With Range("Z22:AB52")
On Error Resume Next
lastrow = .Find(What:="?*", After:=.Cells(1, 1), LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

End With

A co-worker asked me how to programmatically select the first cell of the
last row in a range in Excel 2003 (Windows XP). We found the Help reference
to selecting the last cell in a range, but we couldn't come up with a good
solution for the first cell in the last row, other than a "SendKeys" type of
solution to send "Ctl-down arrow", which is a bit too imprecise.

Neither of us is a strong coder, but he's trying to use a macro to get

Any ideas would be welcome!

Susan Ramlet
**please reply to the newsgroup so others may benefit**

I have retrieved data from mySQL and displayed it on a sheet on a excel workbook with a macro written. I need to add some more codes to the macro so that the displayed data is formatted according to my wish.

Like adjusting column width, changing background colour, changing font, make the name of the column bold (first row of the sheet), freeze the first row.

It would be of much help if i can get some VBA codes to implement these functionalities.

If you would like to see my code of macros please check the following thread.**

Thanks in Advance.


I'm looking for a formula that will take the data from the last row of "workbook A" and put it into "Workbook B." The last row in "Workbook A" will always be changing as data is entered.

Is there a way to do this? Or would I need a Marco to complete this task ?

Here are two sample workbooks.

I need the last row from column "C" in "Disciplinetracker" to populate in "F3" of "ALLTracker"


Hi All,

I have a worksheet with a range that has a set amount of columns (A:AJ) but a variable amount of rows (anywhere from 3:20).

The last row holds the sum of the column. Column A holds a bunch of names.

What I would like to have is a macro that looks at the last row for each column from B to AJ and deletes the entire column if the last row in the column = 0.

My problem is that as soon as you delete one column, the range changes, meaning it skips columns. I need it to look at each column and delete the columns with 0 in the last row without skipping any columns.

I think this means I need a "loop" marco but I have no idea how to do it.

Hi there.

How would i write VB code that looks for the last row in my data table? I'm a total novice with VB but learning slowly..

A while ago I modified a code I found on the web to search for the last row of data then enter in new data based on entry within a form onto the next row. I now need to modify this code to be based upon a specific Row (B to be exact). Can someone please verify this code:

Dim iRow as Long
Dim ws as Worksheet

iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

I then modified the last line to the following in an attempt to specify the search for the last row in Column B

Range("B65535").End(xlUp).Offset(1, 0).Select

But I then get a compile error....

any Ideas what I did wrong...

I am writing a macro to fill column E in this set of data, up to the length of the column of data in A. So the macro should read up to the last row in column A and fill that many rows in column E. So for example when I run the macro, it will read 12 rows for column A and fill all 12 of column E's row with say Tiger.
1	Dog	Gecko		Parrot	
2	Dog	Gecko		Parrot	
3	Dog	Gecko		Parrot	
4	Dog	Gecko		Parrot	
5	Dog	Gecko		Parrot	
6		Gecko	Cat		
7	Dog	Gecko			
8	Dog	Gecko	cat		
9	Dog	Gecko			
10	Dog	Gecko			
11	Dog	Gecko		Parrot	
12	Dog		cat

Any help would be appreciated.

How to prevent user resizing columns and rows in excel (2003) ?


Ollie Riches

Hi All,

I’m not sure the if I’m using the best method, so instead of showing the codes that not working, I’ll just tell you what I’m trying to do.

I have a sheet with about 55 questions, some responses are entered directly into the cells, others use drop down boxes, and check boxes to enter the response into the cells.

Another sheet (that I’ll hide) I have the Question number and a brief text of the question in B111:B167. In C111:C167 I have if statements that basically say if answer cell = 0, then “No Response”, else “Answered”. There’s some more complex if statements if one answer rely on the answer of another question etc, but the basic idea is to let the user know if they didn’t answer a question or if the answer conflicts with a previous question/answer.

I want the range B111:B167 from the hidden sheet copied to Column A of the questionnaire sheet 5 rows after the last used row on the sheet. There is text in cells C261 & S261 at the bottom of the sheet, so currently row 261 is the last row, but users could/might insert or delete rows, when answering questions.

I then also want to copy the error check responses from C111:C167 to column N of the questionnaire sheet 5 rows after the last used row on the sheet, so the question # and response matchup.

I then planed to delete any rows that have “Answered” in column N on the questionnaire Sheet. The Idea being the user can run the macro over and over until no questions remain.

Thanks in advance for any help or suggestions

Hello everyone

Excel 2007 in question

How to show, what in 2002-03 was called, task pane? Cant find it anywhere.

This is a general problem in every version i worked in. While in worksheet all text in cell is displayed OK. But in print preview, and printed, last row of text is not printed, printed half of it...
Problem is solved by increasing that rows hight, but is there some better solution? This is a waste of time, having to check the whole document.

Gridlines! Thay are black dotted lines, just like the thinest line for cell borders. No way of telling what is going on until gridlines are turned off.
I know their color can be changed, but just "this workbook, this worksheet", and than table borders have to be changed to black, but that is A LOT of extra work.
Any way to make them solid, light gray lines like in previous versions?

Some worksheets dont show pictures.
They are there, can be selected, printed, but just aren't visible in worksheet. Bring to front/forword and reset picture gave no result.

Why did i uninstall previous version...

Thanks in advance

Can some one help me or show me how to split first name and last name in excel using macro? Here is what i want to do, i have a column that contain first and last name like the following:
Mike, David
and i want to remove David from the first row and insert a new row and paste the first name in the new row. e.g. i want to sow like this:
Thanks for your help


I have a task that pulls out information from the website. I need to write a vba code to find the last row. Since the no of rows are always not the same. I would need the excel masters help to solve it. Just a small piece of guidance would also help me.

In my work sheet I need to find the word " Function Name: Cleaning" if this is there then I should find the occurence of the word " name". If I find that then the code should copy the values in the column after name till it sees a space ( means no value). Should copy till that and paste that in another work sheet.

Am confused to consturct the if cases here. Have attached a sample file.

Any help is greatly appreciated.

Thanks in advance for the help.

I have done tons of searches on adding a row, which there are many, but have not found what I am looking for.

I have a sheet broken into sections, such as: B4:B10-R4:R10 has data then the next section starts at B13:B19- R4:R10 with 2 blank lines in between.

I have no problem find the last row of the sheet, but I cant seem to find the last row in the section. I have it set up so when I double click on the header cell for each section, it adds a row to the bottom of that specific section, which in this instance, is Row 11 and of course everything gets bumped down. Any help would be appreciated.


I want to select the cell after the last row used in Column A.
for example, I have some data till row A1000 so need to put the formula in Cell A1001 as Code:
each time the data in Column A differs, If the last used row in Column A  is A2500 i need a code to select the cell after the
last row A2501 and insert the formula 
Everyday the data differs, so i want a macro to find next cell of the last row in Column A and insert the formula

I have defined my last row and column by using:

lr = Sheets("week1").Cells(Rows.Count, "A").End(xlUp).Row
LC = Sheets("week1").Cells(1, Columns.Count).End(xlToLeft).Column

Now how do I select the range starting at A2 through the last used column ( LC ) through the last row ( LR )??


Good afternoon,
I am currently building a workbook with 2 tabs.
The first is a data dump tab. The number of rows used in this tab will change each time it is used.
The second tab currently has a header row and in cells A2-H2 are formulas based on data in the first tab.
I am creating a macro to autofill those formulas, but I don't know how to determine what is the last row of data in the 1st tab and use that as reference in my macro.

Any ideas?

I want to know if thi is possible to do in a macro. I have data in colunm A and then a formula in Column D. The problem is each month the data in column A increases into more rows. I want to go in and copy the formula from last row in Column D and carry it down to till it finds the last row in Column A.

Column A has data in rows 1-25.
Column D has a formula in row 1-20.

Each month the rows would increase and need to carry the formula down.

I know I can figure out the last row in Column A by using this

LastRow = Range("A" & Rows.Count).End(xlUp).Row

Is is possible to select the blank rows in Column D until the last row is A and then do a paste formula?

I have this to get to my first blank row in column D --- Cells(Rows.Count, 43).End(xlUp)(2).Select

I'm not sure if it's possible to combine that with the Last Row function to select in this case cells D20-25 then next month it would select D25-DXX


No luck finding an answer? You could always try Google.