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

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

daz457

New Member
Joined
Sep 21, 2020
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
should have mentioned , col A will always have data in it, example screenshot A22 would be the first complete empty row
 

Fluff

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,434
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Assuming that we can always use column A to locate the last populated row, try this code:
VBA 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

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

ADVERTISEMENT

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

New Member
Joined
Sep 21, 2020
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
Welcome to the Board!

Assuming that we can always use column A to locate the last populated row, try this code:
VBA 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

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

ADVERTISEMENT

Glad we could help & thanks for the feedback.
 

daz457

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,434
Office Version
  1. 365
Platform
  1. Windows
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

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

Watch MrExcel Video

Forum statistics

Threads
1,118,594
Messages
5,573,118
Members
412,504
Latest member
Confused exl
Top