-1

How can I create a program that sorts through a spreadsheet finds the last line of data then copies/pastes in new text in the next available row?

Excellll
  • 5,609
  • 4
  • 38
  • 55
Martin
  • 1
  • 4
    Have you tried anything? – Jean-François Corbett Sep 20 '11 at 18:45
  • And does your data have blanks, or is it contiguous? – RonnieDickson Sep 20 '11 at 19:13
  • 1
    This question has already been answered: A quick search of StackOverFlow for "vba excel find last row" gives this nice solution:http://stackoverflow.com/questions/4092329/find-bottom-of-excel-worksheet-in-vba – Jon49 Sep 20 '11 at 19:56
  • The data does have blanks, however column A is always populated. So I was thinking of having a program search through each row and just column A to find a blank. Once a blank is found, then it can paste in the new data. – Martin Sep 20 '11 at 19:59
  • The link provided by Jon49 includes a reference for sparse data. – Fionnuala Sep 20 '11 at 20:03
  • Thanks everyone, This is the code that I ended up using after looking at the references. Range("A65536").End(xlUp).Offset(1).Select – Martin Sep 20 '11 at 20:26
  • You dont need to select cells to work with them. Your method is good for finding the last used cell in a column. For a better method of finding the actual last used cell take a look here: http://stackoverflow.com/questions/7423022/excel-getting-the-actual-usedrange/7427466#comment-8980597 – Reafidy Sep 20 '11 at 21:16

2 Answers2

0

Bruno's method will not work in later versions of Excel if you have data in Column A after Row 65536. Additionally, if you have data at the end of the sheet (regardless of version), if you do xlUp, then you'll skip past the true end.

This method relies on the popular method with a quick check for the very last row.

Sub getlin2()

Dim lin As Long
Dim Sht As String
Dim Col As String

Col = "A"
Sht = "Sheet1"

lin = Sheets(Sht).Rows.Count
If Sheets(Sht).Range(Col & lin).Value = vbNullString Then
    lin = Sheets(Sht).Range(Col & Sheets(Sht).Rows.Count).End(xlUp).Offset(1, 0).Row
End If

MsgBox lin

End Sub
Zairja
  • 1,441
  • 12
  • 31
0

This UDF receive a Sheet name at first parameter, and a column letter to find a row, this return a next line available.

Function GetLin(Sht As String, Col As String)
'officevb.com
Dim UltLinPlan As Long

'Get a last row in sheet
UltLinPlan = Sheets(Sht).Range("A65536").End(xlDown).Row

'Return this number
GetLin = Sheets(Sht).Range(Col & UltLinPlan).End(xlUp).Offset(1, 0).Row

End Function

To use is very simple

dim x as long

x=GetLin("Sheet1","A")

This sample returns a next line in column A from Sheet1

[]'s

Bruno Leite
  • 1,403
  • 13
  • 17
  • 1
    Replace Range("A65536") with Cells(Rows.Count,1) to be Excel version agnostic, since 65536 is no longer the last row in Excel 2007 and later. – RonnieDickson Sep 21 '11 at 03:25
  • Ronnie, this version work´s in Office 2007/2010. Verify this line ´UltLinPlan = Sheets(Sht).Range("A65536").End(xlDown).Row´ , the direction is down! – Bruno Leite Sep 21 '11 at 12:33
  • It should be xlUp. This is a standard Excel practice - find the last row, and mimic CTRL+ up arrow to then find the last row. Things will work without my commented change if you have < 65,536 rows. – RonnieDickson Sep 21 '11 at 21:23