select first blank cell in row -insert value and loop to next row

[daz457]

I have a table of data approx 25 columns and roughly 100 rows.
Each row has data but also blank cells(see image)
What I would like to be able to do is run a code that looks at each row in turn.
It would find the first empty cell in that row
Insert "1" into this cell , disregard any other cells on this row and then go to the row below and do the same.
Loop until it does not find any more rows to look at.

I have trawled lots of code, I can find the first empty cell in a given row and then manually insert a value, but cannot loop down to the next row, very new to VBA, I usually record macros and tweak as required, recording and adding bits of code as I go.

I am sure this is not too difficult but a bit beyond me

Thanks in advance

 

[daz457]

should have mentioned , col A will always have data in it, example screenshot A22 would be the first complete empty row

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub daz()
   Dim i As Long
   
   For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
      On Error Resume Next
      Rows(i).SpecialCells(xlBlanks)(1).Value = 1
      If err.Number <> 0 Then Cells(i, Columns.Count).End(xlToLeft).Offset(, 1).Value = 1
      On Error GoTo 0
   Next i
End Sub

 

[Joe4]

Welcome to the Board!

Assuming that we can always use column A to locate the last populated row, try this code:

Sub MyMacro()

    Dim lrow As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column A with data
    lrow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows
    For r = 1 To lrow
'       Put a "1" after the last populated cell in row
        Cells(r, Columns.Count).End(xlToLeft).Offset(0, 1) = 1
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[daz457]

Sub daz() Dim i As Long For i = 1 To Range("A" & Rows.Count).End(xlUp).Row On Error Resume Next Rows(i).SpecialCells(xlBlanks)(1).Value = 1 If err.Number <> 0 Then Cells(i, Columns.Count).End(xlToLeft).Offset(, 1).Value = 1 On Error GoTo 0 Next i End Sub

that works spot on thanks, and thanks for the welcome to the group !

 

[daz457]

Welcome to the Board!

Assuming that we can always use column A to locate the last populated row, try this code:

Sub MyMacro()

    Dim lrow As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last row in column A with data
    lrow = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Loop through all rows
    For r = 1 To lrow
'       Put a "1" after the last populated cell in row
        Cells(r, Columns.Count).End(xlToLeft).Offset(0, 1) = 1
    Next r
   
    Application.ScreenUpdating = True
   
End Sub

Hi Joe,
for some reason this one is skipping some blanks and adding a 1 to the end of the data range in the rows? not sure if the referenceing Col A is the issue, the other poster has hit the nail on the head, but many thanks for your reply

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[daz457]

more than welcome, as a bit of an insight can you explain the steps that make this work, its very nice to have the finished code but if I could understand what does what that would be great

 

[Joe4]

Hi Joe,
for some reason this one is skipping some blanks and adding a 1 to the end of the data range in the rows? not sure if the referenceing Col A is the issue, the other poster has hit the nail on the head, but many thanks for your reply

Yes, I misunderstood the question. I thought you wanted the one after the last entry in the row, instead of in the first blank cell in that row.
Many times, those are the same place, but not when there are blanks in the middle of the data.
Sorry about that.

 

[daz457]

Yes, I misunderstood the question. I thought you wanted the one after the last entry in the row, instead of in the first blank cell in that row.
Many times, those are the same place, but not when there are blanks in the middle of the data.
Sorry about that.

I thought that the blanks in the middle might throw a spanner in there !

thanks again