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

 

[Fluff]

HTH

Sub daz()
   Dim i As Long
   
   'loops through the data from from first row to last used cell in column A
   For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
      'prevents an error if there are no blank cells in the used range
      On Error Resume Next
      'finds the first blank cell in the used range & inserts a 1, will cause an error if there are no blanks
      Rows(i).SpecialCells(xlBlanks)(1).Value = 1
      'check if there was an error on the row above & inserts a 1 into the first unused column in that row
      If err.Number <> 0 Then Cells(i, Columns.Count).End(xlToLeft).Offset(, 1).Value = 1
      'resets the error handling back to befault
      On Error GoTo 0
   Next i
End Sub

 

[daz457]

HTH

Sub daz()
   Dim i As Long
  
   'loops through the data from from first row to last used cell in column A
   For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
      'prevents an error if there are no blank cells in the used range
      On Error Resume Next
      'finds the first blank cell in the used range & inserts a 1, will cause an error if there are no blanks
      Rows(i).SpecialCells(xlBlanks)(1).Value = 1
      'check if there was an error on the row above & inserts a 1 into the first unused column in that row
      If err.Number <> 0 Then Cells(i, Columns.Count).End(xlToLeft).Offset(, 1).Value = 1
      'resets the error handling back to befault
      On Error GoTo 0
   Next i
End Sub

thats great thanks so much!

 

[Fluff]

You're welcome.

 

[daz457]

whilst I have got your ear one more little ask?

My spreadsheet now has every first blank cell in each row with a number 1 in it., so looking down each column there is a series of number 1's what I was doing next was going to the bottom of each column and using sum function to sum up each column, some columns would have a zero total and I would delete the column, just leaving the columns that had a value greater than zero, this is an easy manual operation , it was putting the number 1's in the empty cells that was the time consuming part of the task, is there a quick bit of code I could insert at the end of my other ones to allow the sum function to be done, I have already worked out how to delete rows and columns based on the value of a cell, so just the going to the bottom of each colum and summing up that column is all I need.
thanks in advance

 

[daz457]

the rows are not always the same amount so cannot use a specific range to do this otherwise it would be straightforward
cheers

 

[Fluff]

How about

Sub daz()
   Dim UsdCols As Long
   
   UsdCols = Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Column
   With Range("A" & Rows.Count).End(xlUp).Offset(1)
      .Resize(, UsdCols).FormulaR1C1 = "=sum(r1c:r[-1]c)"
   End With
End Sub

 

[daz457]

works fine, probably should have mentioned that only need to sum col G to T , the others have all sorts of dates and other data!

 

[Fluff]

In that case use

Sub daz()
   With Range("A" & Rows.Count).End(xlUp).Offset(1)
      .Offset(, 6).Resize(, 14).FormulaR1C1 = "=sum(r1c:r[-1]c)"
   End With
End Sub

 

[daz457]

In that case use

Sub daz()
   With Range("A" & Rows.Count).End(xlUp).Offset(1)
      .Offset(, 6).Resize(, 14).FormulaR1C1 = "=sum(r1c:r[-1]c)"
   End With
End Sub

spot on, I wish I had found this forum ages ago ,I have done a lot of googling and headscratching doing this project!
Once again many thanks you are a lifesaver

 

[Fluff]

My pleasure.