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

daz457

New Member
Joined
Sep 21, 2020
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
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
 

Attachments

  • Capture.JPG
    Capture.JPG
    108.2 KB · Views: 9

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,778
Office Version
  1. 365
Platform
  1. Windows
HTH
VBA Code:
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

daz457

New Member
Joined
Sep 21, 2020
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
HTH
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,778
Office Version
  1. 365
Platform
  1. Windows
You're welcome.
 

daz457

New Member
Joined
Sep 21, 2020
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Sep 21, 2020
Messages
16
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Capture.JPG


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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,778
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

New Member
Joined
Sep 21, 2020
Messages
16
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,778
Office Version
  1. 365
Platform
  1. Windows
In that case use
VBA Code:
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

New Member
Joined
Sep 21, 2020
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
In that case use
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,778
Office Version
  1. 365
Platform
  1. Windows
My pleasure.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,587
Messages
5,573,074
Members
412,502
Latest member
HMilne
Top