Macro to fill blank cells in multiple worksheets with values in a cell range in respect to each worksheet

Ay Sticky

New Member
Joined
Oct 18, 2021
Messages
37
Office Version
  1. 2016
Platform
  1. Windows
I have a 2 column data with varying number of rows across the worksheets. All values in each worksheets are different and independent. The data consist of blank spaces and for each row with blank space, there are no values across their 2 columns. Now I need a VBA to copy the values in row 1 (the values are in 2 columns) and insert into the blank rows until the last blank row before the last row with data. And I want this to process across all worksheets minding that the data in these worksheets differs from each other.

For example
A B
1 4.4 5.4
2
3
4 4.2 6.7

Row 2 and 3 consist of blank spaces and I need fill in their respective 2 empty columns with values from row 1 (A1:B1). Also the fillings should not exceed the last blank row before the last row with data, that is row 4.

This should be done to the all worksheets in respect to the values in their first row to be used in filling their blank spaces.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Please upload example data and Desired result with method I posted below of my post.
 

Ay Sticky

New Member
Joined
Oct 18, 2021
Messages
37
Office Version
  1. 2016
Platform
  1. Windows
Screenshot_20211020-080631.png
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this:
VBA Code:
Sub Test()
Dim i As Long, j As Long, K As Long, St As String, Lr As Long, Ws As Worksheet
For Each Ws In Worksheets
Lr = Ws.Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To Lr - 1
If Ws.Range("A" & i + 1).Value = "" Then
K = Ws.Range("A" & i).End(xlDown).Row - 1
Ws.Range("A" & i + 1 & ":B" & K).Value = Ws.Range("A" & i & ":B" & i).Value
i = K
End If
Next i
End Sub
 

Ay Sticky

New Member
Joined
Oct 18, 2021
Messages
37
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Try this:
VBA Code:
Sub Test()
Dim i As Long, j As Long, K As Long, St As String, Lr As Long, Ws As Worksheet
For Each Ws In Worksheets
Lr = Ws.Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To Lr - 1
If Ws.Range("A" & i + 1).Value = "" Then
K = Ws.Range("A" & i).End(xlDown).Row - 1
Ws.Range("A" & i + 1 & ":B" & K).Value = Ws.Range("A" & i & ":B" & i).Value
i = K
End If
Next i
End Sub
Running the above code, I got a dialog box display error that displays as below.

Compile error:
For without Next
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Sorry my fault. After Next i Line add:
VBA Code:
Next Ws
 

Ay Sticky

New Member
Joined
Oct 18, 2021
Messages
37
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

The code runs well now, but did not do the job. Instead, it was filling those blank rows with values above each blanks. I only intend to fill those blanks with values present in first row only. That is, using values in A1:B1 to fill all the blank rows. The blank rows are very much, more than just the 4 rows in the screenshot I posted earlier. For example in worksheet (1), I may have up to 500 rows of data inclusive of these empty rows.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
if you want only all Blank Cells with Row 1 Data Try this:
VBA Code:
Sub Test()
Dim i As Long, j As Long, K As Long, St As String, Lr As Long, Ws As Worksheet
For Each Ws In Worksheets
Lr = Ws.Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To Lr - 1
If Ws.Range("A" & i + 1).Value = "" Then
K = Ws.Range("A" & i).End(xlDown).Row - 1
Ws.Range("A" & i + 1 & ":B" & K).Value = Ws.Range("A1:B1").Value
i = K
End If
Next i
Next Ws
End Sub
 
Solution

Ay Sticky

New Member
Joined
Oct 18, 2021
Messages
37
Office Version
  1. 2016
Platform
  1. Windows
if you want only all Blank Cells with Row 1 Data Try this:
VBA Code:
Sub Test()
Dim i As Long, j As Long, K As Long, St As String, Lr As Long, Ws As Worksheet
For Each Ws In Worksheets
Lr = Ws.Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To Lr - 1
If Ws.Range("A" & i + 1).Value = "" Then
K = Ws.Range("A" & i).End(xlDown).Row - 1
Ws.Range("A" & i + 1 & ":B" & K).Value = Ws.Range("A1:B1").Value
i = K
End If
Next i
Next Ws
End Sub
This worked perfectly! Thank you many times.
 

Forum statistics

Threads
1,147,748
Messages
5,742,971
Members
423,769
Latest member
LongToast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top