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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Please upload example data and Desired result with method I posted below of my post.
 
Upvote 0
Screenshot_20211020-080631.png
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Sorry my fault. After Next i Line add:
VBA Code:
Next Ws
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Solution
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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
Back
Top