Finding the last row of data

[robert2290]

Hello,

I am in need of a few lines that will help my macro determine the last row of data. However, there are a few variables that make this a little bit more complicated. The data is arranged as shown below.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]kdjljfdlldlkd[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]kljfjdslfja[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]g[/TD]
[TD]1234[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]e[/TD]
[TD]5647[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]f[/TD]
[TD]6646[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]f[/TD]
[TD]5665[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]s[/TD]
[TD]jdkjfaslgfj[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]d[/TD]
[TD]dfldslfsdlgf[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]d[/TD]
[TD]djjdlsfglksdf[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I need is to somehow find the row right below where it says "5665." I will then be inserting information into that row (I have the code for that). After the information has been added, I will want the macro to add a new row below where the information was added (keeping the spacing between that row and the row containing "jdkjfaslgfj" the same). Then I be repeating the process.

Any help would be greatly appreciated.

 

[patel45]

lastrow = Cells(Rows.Count, "B").End(xlUp).Row

 

[Rick Rothstein]

What I need is to somehow find the row right below where it says "5665."... Then I be repeating the process.

It is not clear what you need to be finding. Each occurrence of "5665"? Each last number in a group of numbers? The cells above each blank row whether it contains a text or a number? Some other criteria?

 

[Jeffrey Smith]

Is it only when 5665 is in column C and then a blank row below? Or is it when there is only a blank row?

This code will look in all the rows below the selected cell for a blank row. It checks to see if three columns are blank. Alter it to your whim.

Sub FindBlankRow()
  Dim Rng As Range
  Dim C As Range
  
  Set Rng = Range(ActiveCell, ActiveCell.Offset(65000, 0).End(xlUp))
  
  For Each C In Rng
    If Application.WorksheetFunction.CountBlank(Range(C, C.Offset(0, 2))) = 3 Then
      C.EntireRow.Insert
      'Add some data here
      Exit For
    End If
  Next C
  
End Sub

 

[robert2290]

I'm sorry, I did not make it clear enough. The format of the spreadsheet will be the same. There will be basically three sections with each one of them separated by a space. I want the code to find the last line of the second section (i.e., 5665 for this example). Many lines will be inserted into the bottom of this second section so it will grow over time.

 

[Rick Rothstein]

I'm sorry, I did not make it clear enough. The format of the spreadsheet will be the same. There will be basically three sections with each one of them separated by a space. I want the code to find the last line of the second section (i.e., 5665 for this example). Many lines will be inserted into the bottom of this second section so it will grow over time.

Give this code a try...

Sub InsertRowAfterSecondSection()
  Const FirstDataRow As Long = 2
  Const DataColumn As String = "C"
  Range(Cells(FirstDataRow, DataColumn), Cells(Rows.Count, DataColumn).End(xlUp)). _
              SpecialCells(xlCellTypeBlanks).Areas(2)(1).Offset(1).EntireRow.Insert
End Sub

Just change the two constants (the Const statements) to reflect your actual data setup.

 

[robert2290]

Thanks for your suggestion, but that's not quite what I'm looking for.

My structure is as follows:

row = 9 to 57
last_row= (insert code here)
send information from other workbook into that "last_row"
insert new line just below the last_row
next

 

[Rick Rothstein]

Thanks for your suggestion, but that's not quite what I'm looking for.

My structure is as follows:

row = 9 to 57
last_row= (insert code here)
send information from other workbook into that "last_row"
insert new line just below the last_row
next

The idea of my code is to run it before you run whatever code you have now... my code will insert the extra row and your existing code can then fill in the first blank it finds. Of course, you have to change the FirstDataRow constant from my "guess" of 2 to the value of 9 which you only just mentioned in your last response (also, don't forget to change the DataColumn constant if your text is not in Column "C" (which I also had to guess at).