MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

return last row?

  Asked By: Jayden    Date: Jan 18    Category: MS Office    Views: 2163

Have just done my first XL VBA Macro, it basically moves down column
1 and if a cell is blank, gives it the value of the cell above.

I'd like to refine it so that I didn't need to ask the user to scroll
to the bottom and enter the number of the final used row.

So... is there a way of returning the bottom (used) row in a
It would have to be the bottom of all columns, not just say, the
bottom row in column 1.

Private Sub BS()

Dim row As Integer
Dim col As Integer
Dim x As Integer

x = InputBox("enter number of rows")

For row = 1 To x - 1
For col = 1 To 1
If Cells(row + 1, col) = "" Then
Cells(row + 1, col) = Cells(row, col)
End If
Next col
Next row

End Sub



10 Answers Found

Answer #1    Answered By: Mike Stephens     Answered On: Jan 18

try this.....

lrow = worksheet("sheet1").Cells.specialcells(xlcelltypelastcell).row

that will give you the last row  number in the worksheet

Answer #2    Answered By: Adalric Fischer     Answered On: Jan 18

I had a few teething problems, and needed to add an 's' to the end of

but it worked a treat - thanks!

*runs off to investigate other specialcell properties :-)

Answer #3    Answered By: Julia Flores     Answered On: Jan 18

Sub Macro1()
' Macro to find the last Row No of a typed column  cells.
' Macro Created on 1/24/2006 by Mactikus
' Assign shortcut key Ctrl+Shift+S
RowNo = Cells.SpecialCells(xlCellTypeLastCell).Row
ColNo = Cells.SpecialCells(xlCellTypeLastCell).Column
For i = RowNo To 1 Step -1
Cells(1, 5).Formula = "=COUNTIF(" & i & ":" & i & ","""")"
If ColNo > Cells(1, 5).Value Then
GoTo BreakNext
End If
Next i
Cells(1, 6) = i
End Sub

I am using cells A5 to store the COUNTIF function and A6 to store
the last Rown No.

Answer #4    Answered By: Jarvia Miller     Answered On: Jan 18

Sorry, my mistake not A5 and A6, shoukd be E1 and F1 instead.

Answer #5    Answered By: Allan Bailey     Answered On: Jan 18

You seem to be creating a sledgehammer to crack a nut here.

Try the following line:


Answer #6    Answered By: Baylen Smith     Answered On: Jan 18

I am sorry you do need a sledgehammer. Try simulate this ~ Do an
Edit/Delete/All for the cell  just below the so called last cell (say
C10) your solution will give 11 and not 10. What we are detecting is
the last row  that can be detected by the eyes.

Answer #7    Answered By: Lughaidh Fischer     Answered On: Jan 18

I am just about to go out for an appointment, but how about this as an

I am assuming that the data is principally a single block of data, no
complete empty rows or complete empty columns.

Name the top left hand cell  something like "StartCell"

Record a macro  to give you a basic procedure which you can refeine later if
need be.

edit, Goto, select startcell

Hold Ctrl + * to select current Region

Edit, goto, special, blank  cells

That will select all the blank cells in the current region.

In the active cell type the = sign and select the cell immediate above

Press Ctrl + enter  to enter the relative formula into each of the blank

To polish off, Edit, Goto and select Startcell again.

The resulting macro should give you a good basis to develop further if you
need to.

Let me know how you get on.

I will check the list in the morning.

Answer #8    Answered By: Aalia Arain     Answered On: Jan 18

What you have suggested is a similar process of identifying the
Matrix area of the occupied spread sheet. Being a beginner of Ms
Excel and VBA I would like to thank you for showing me a way that I
have never used before. The process of using
Edit/Goto/Special/Blanks highlight all blank  cells and also show the
entire matrix of the occupied spread sheet. Your suggestion of
introducing cells with formula is not favorable because of the
possibility that cells might be linked to other sheets or workbook.

We are facing a problem that looks like an Excel design problem.
Allow me to explain.

(1)First let us create a new sheet with some data scattered within
the matrix A1:C10.
(2)If you do an Edit/Goto/Special/Blanks you will clearly see all
blank cells highlighted within the marked matrix A1:C10.
(3)Highlight A11:C13 and do an Edit/Clear/All.
(4)Repeat Edit/Goto/Special/Blanks you will see all blank cells
highlighted within the marked matrix A1:C13.
(5)Highlight rows 11 to 13 and do a delete
(6)The rows 11 to 13 are new inserted rows and should have no
information of "Clear All" for cells A11:C13 but if you repeat
Edit/Goto/Special/Blanks you will get the same result as in (4)

Feel like telling Microsoft that they make us creating a
sledgehammer to crack a nut.

Answer #9    Answered By: Terence Mitchell     Answered On: Jan 18

I am not sure I understand what you are getting at here.
Irrespective of whether you use Edit-Clear all or not on a selected range,
when you select the Edit-Goto-Special-Blanks, that will select all the blank
cells in the current selected range.

A neat manual trick here is to use standard entry procedures.

So a demo for you and anybody else interested here:

Select a new sheet

Type 123 into cells:

A1, A4, A7

B2, B5, B8

C3, C6, C9

Click into any cell  within the range say  B5

Press the Ctrl+* to select the current range

Select Edit-Goto-Special-Blanks

Note the active cell, should be B1

Type in 999


you press Enter, the next cell is selected in the selected range moving down
to the end of the current column  & then up to the top of the next column


you press Tab, the next cell is selected in the selected range moving to the
right to the end of the current row  & then down to the start of the next row

NB: if you make a mistake, hold the Shift key and press either Tab or Enter
to move back. If you click into the area with the mouse or use arrows you
will loose your selection & have to start again, but omitting any cells that
you have successfully entered data into.

Answer #10    Answered By: Adalwin Fischer     Answered On: Jan 18

I have to say  sorry again. You are making the assumption that there
will be no blank  rows in between. Try delete cell  B5 and do the
Ctrl+* again. You will know what I mean.

I will be away on holiday for a week and will reply any message that
is meant for me after the holiday.

Didn't find what you were looking for? Find more on return last row? Or get search suggestion and latest updates.