Next row select

[charlieb0y]

Hi can someone please help. I have some code that finds the next empty row and inputs data from a form, which all works fine. But is there a way to tell it to ignore colums F to I when looking for the next empty row. As I want to populate these colums with data. When I run it now with data in those columns it finds the next empty row down from that data.

Sub Update_Rows()
    Dim NextRow As Integer
    Dim CurrentRow As Object
 
 
    'Update the database. Determine the first empty row.
    NextRow = Sheets("sheet1").Range("A1").CurrentRegion.Rows.Count
    Set CurrentRow = Sheets("sheet1").Range("A1").Offset(NextRow)
 
 
 
    'Insert data from User Form
    CurrentRow.Offset(0, 0) = frmISSQuote.tbSite.Text
    CurrentRow.Offset(0, 1) = frmISSQuote.tbRefNumber.Text
    CurrentRow.Offset(0, 2) = frmISSQuote.tbResource.Text
    CurrentRow.Offset(0, 3) = frmISSQuote.tbRate.Text
    CurrentRow.Offset(0, 4) = frmISSQuote.tbHours.Text
    'CurrentRow.Offset(0, 5) =
    'CurrentRow.Offset(0, 6) =
    'CurrentRow.Offset(0, 7) =
    'CurrentRow.Offset(0, 8) =
    End Sub

Many Thanks

 

[namliam]

It doesnt use column F?? It used a "currentregion" select to find the number of rows occupied... only special circumstances cause it to 'break'

 

[stopher]

As mailman say, the region is being specified by currentregion (a bit like looking for the boundaries of a block of data) so any data in the next columns/row will be considered part of the currentregion until it reaches blank areas. Blank columns or rows will create the boundary so an easy answer would be to add a blank column between cols e and f. Thus the currentregion will only extend as far as col E.

Another way: I'm not familiar with many of the Excel VBA functions but you could apply a simple loop to check if the A column has entries. So the following will look for next empty cell in the A column:

    Dim NextRow As Integer
    Dim CurrentRow As Object
    
    'Update the database. Determine the first empty row.
    NextRow = 1
    Do Until (Sheets("sheet1").Range("A1").Offset(NextRow).Value) = Null Or Sheets("sheet1").Range("A1").Offset(NextRow).Value = ""
        NextRow = NextRow + 1
    Loop
    Set CurrentRow = Sheets("sheet1").Range("A1").Offset(NextRow)

    'Insert data from User Form
    CurrentRow.Offset(0, 0) = frmISSQuote.tbSite.Text
    CurrentRow.Offset(0, 1) = frmISSQuote.tbRefNumber.Text
    CurrentRow.Offset(0, 2) = frmISSQuote.tbResource.Text
    CurrentRow.Offset(0, 3) = frmISSQuote.tbRate.Text
    CurrentRow.Offset(0, 4) = frmISSQuote.tbHours.Text
    'CurrentRow.Offset(0, 5) =
    'CurrentRow.Offset(0, 6) =
    'CurrentRow.Offset(0, 7) =
    'CurrentRow.Offset(0, 8) =

hth
Chris

 

[charlieb0y]

Hi Stephen

Thanks for the reply solved it, added an empty column in and then hid it. Works fine now.

 

[namliam]

Hi Stephen

Is it me or??? who is Stephen?