CODEWITHSUNDEEP
vba
Sid. T.
Sid. T.

Reputation: 93

Offset rows into a range - Vba

anything I can do to help shorten this code?

Dim iRow As Long
Dim Lastrow As Long
Dim ws As Worksheet
Set ws = Worksheets("FirstShift")

Lastrow = ws.Range("c:v").Find("*", , , , xlByRows, xlPrevious).Row

'This code here:

 For iRow = 16 To Lastrow
 If ws.Cells(iRow, 9).Value = "" And ws.Cells(iRow, 9).Offset(0, 1).Value = 
 "" And ws.Cells(iRow, 9).Offset(0, 2).Value = "" And ws.Cells(iRow, 
 9).Offset(0, 3).Value = "" And ws.Cells(iRow, 9).Offset(0, 4).Value = "" 
 And ws.Cells(iRow, 9).Offset(0, 5).Value = "" And ws.Cells(iRow, 
 9).Offset(0, 6).Value = "" And ws.Cells(iRow, 9).Offset(0, 7).Value = "" 
 And ws.Cells(iRow, 9).Offset(0, 8).Value = "" And ws.Cells(iRow, 
 9).Offset(0, 9).Value = "" And ws.Cells(iRow, 9).Offset(0, 10).Value = "" 
 Then
    ws.Cells(iRow, 9).Value = Me.textbox_Lane1.Value
 End If
Next iRow

For iRow = 16 To Lastrow
If ws.Cells(iRow, 10).Value = "" And ws.Cells(iRow, 10).Offset(0, 1).Value 
= "" And ws.Cells(iRow, 10).Offset(0, 2).Value = "" And ws.Cells(iRow, 
10).Offset(0, 3).Value = "" And ws.Cells(iRow, 10).Offset(0, 4).Value = "" 
And ws.Cells(iRow, 10).Offset(0, 5).Value = "" And ws.Cells(iRow, 
10).Offset(0, 6).Value = "" And ws.Cells(iRow, 10).Offset(0, 7).Value = "" 
And ws.Cells(iRow, 10).Offset(0, 8).Value = "" And ws.Cells(iRow, 
10).Offset(0, 9).Value = "" And ws.Cells(iRow, 10).Offset(0, -1).Value = "" 
Then
   ws.Cells(iRow, 10).Value = Me.textbox_Lane2.Value
End If
Next iRow

'To here...

I have 11 data cells in which I need to keep them in a line or row when the data is entered. The challenge is that sometimes not all 11 data cells will have data so in order for me to keep each data input in a line or row, this is the code I came up which works.

Now I have a few more data cells to add and this code is just getting way too long and ridiculous. Help is appreciated! Thanks!

Upvotes: 0

Views: 57

Answers (1)

DisplayName
DisplayName

Reputation: 13386

It seems you could simply code

With ws
    Lastrow = .Range("c:v").Find("*", , , , xlByRows, xlPrevious).Row

    For iRow = 16 To Lastrow
        If WorksheetFunction.CountA(.Cells(iRow, 9).Resize(0, 11)) =0 Then .Cells(iRow, 9).Value = Me.textbox_Lane1.Value
    Next
End With

Upvotes: 1

Related Questions