Excel - VBA填充第一和最后一个值之间的单元格 [英] Excel - VBA fill in cells between 1st and Last value

问题描述

我试图使用VBA来填充左边的值的行中的所有空白单元格，除了我只想填充行中第一个和最后一个值之间的空白单元格（不包括第1行和第列A，它们是标识符）。

一旦达到值的最后一列，我一直在努力停止循环（随着每行的更改），而不是一直运行在工作表的最后一列。

最初这被标记为重复（

感谢大家所有的帮助！

解决方案

这里是另一个解决方案（只是给你一些var iety）：

  Option Explicit 
 
 Sub fillInTheBlanks（）
 
 Dim lngRow As Long 
 Dim ws As Worksheet 
 Dim lngColumn As Long 
 Dim bolStart As Boolean 
 Dim lngLastColumn As Long 
 Dim dblTempValue As Double 
 Dim arrSheetCopy As变量
 
设置ws = ThisWorkbook.Worksheets（Sheet1）
 arrSheetCopy = ws.Range（ws.Cells（3，1），ws.Cells（ws.Cells（ws.Rows .Count，A）。End（xlUp）.Row，ws.UsedRange.Columns.Count））Value2 
 
对于lngRow = LBound（arrSheetCopy，1）到UBound（arrSheetCopy，1） 
 bolStart = False 
 lngLastColumn = 0 
对于lngColumn = LBound（arrSheetCopy，2）到UBound（arrSheetCopy，2）
如果不是arrSheetCopy（lngRow，lngColumn）= vbEmpty然后lngLastColumn = lngColumn 
下一个lngColumn 
对于lngColumn = LBound（arrSheetCopy，2）到lngL​​astColumn 
如果arrSheetCopy（lngRow，lngColumn）= vbEmpty和bolStart然后
 arrSheetCopy（lngRow，lngColumn）= dblTempV alue 
 Else 
如果不是arrSheetCopy（lngRow，lngColumn）= vbEmpty和IsNumeric（arrSheetCopy（lngRow，lngColumn））然后
 bolStart = True 
 dblTempValue = CDbl（arrSheetCopy（lngRow， lngColumn））
 End If 
 End If 
 Next lngColumn 
 Next lngRow 
 
 ws.Range（A3）。调整大小（UBound（arrSheetCopy， 1），UBound（arrSheetCopy，2））Value2 = arrSheetCopy 
 
 End Sub 
  

这可能是最快的解决方案（尽管与其他解决方案相比，它似乎有点笨重，更多的代码行）。这是因为这个解决方案在内存中大部分的工作，而不是在工作表上。整个工作表被加载到变量中，然后在将结果（变量）写回到工作表之前对变量进行工作。所以，如果你有速度问题，那么你可能想考虑使用这个解决方案。

I am attempting to use VBA to fill all blank cells in rows with the value to the left, with the exception that I only want to fill the blank cells between the first and last value in the row (not including row 1 and column A, which are identifiers).

I've struggled with getting the loop to stop once the last column with a value has been reached (as this changes with each row), rather than running all the way through the last column on the sheet.

Originally this was marked as duplicate (Autofill when there are blank values), but this does not solve the mentioned problem. This continues until the sheet ends. As seen in the picture below, the fill should stop when the last value is reached.

I am searching for a solution that will allow me to do this for an entire sheet at once, even though the data ends in different columns throughout the sheet. There are 1000+ rows, so running for each row could be quite tedious.

I've been using this code to fill the data (excluding the 1st row and column). But this is where I am not sure how to get it to stop at the last value in the row.

Sub test()
With ThisWorkbook.Sheets("Sheet1").Range("A:A")
    With Range(.Cells(2, 2), .Cells(.Rows.Count, 36).End(xlUp))
        With .Offset(0, 1)
            .Value = .Value
            On Error Resume Next
            .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[-1]&"""""
            On Error GoTo 0
            .Value = .Value
        End With
    End With
End With
End Sub

If my explanation was not clear, This is a sample and the output I am trying to create

Thank you all so much in advance for all your help!

解决方案

And here is yet another solution (just to give you some variety):

Option Explicit

Sub fillInTheBlanks()

Dim lngRow As Long
Dim ws As Worksheet
Dim lngColumn As Long
Dim bolStart As Boolean
Dim lngLastColumn As Long
Dim dblTempValue As Double
Dim arrSheetCopy As Variant

Set ws = ThisWorkbook.Worksheets("Sheet1")
arrSheetCopy = ws.Range(ws.Cells(3, 1), ws.Cells(ws.Cells(ws.Rows.Count, "A").End(xlUp).Row, ws.UsedRange.Columns.Count)).Value2

For lngRow = LBound(arrSheetCopy, 1) To UBound(arrSheetCopy, 1)
    bolStart = False
    lngLastColumn = 0
    For lngColumn = LBound(arrSheetCopy, 2) To UBound(arrSheetCopy, 2)
        If Not arrSheetCopy(lngRow, lngColumn) = vbEmpty Then lngLastColumn = lngColumn
    Next lngColumn
    For lngColumn = LBound(arrSheetCopy, 2) To lngLastColumn
        If arrSheetCopy(lngRow, lngColumn) = vbEmpty And bolStart Then
            arrSheetCopy(lngRow, lngColumn) = dblTempValue
        Else
            If Not arrSheetCopy(lngRow, lngColumn) = vbEmpty And IsNumeric(arrSheetCopy(lngRow, lngColumn)) Then
                bolStart = True
                dblTempValue = CDbl(arrSheetCopy(lngRow, lngColumn))
            End If
        End If
    Next lngColumn
Next lngRow

ws.Range("A3").Resize(UBound(arrSheetCopy, 1), UBound(arrSheetCopy, 2)).Value2 = arrSheetCopy

End Sub

This one is probably the fastest solution (even though it seems a bit bulky with much more lines of code when compared to the other solutions). That's due to the fact that this solution is doing most of the work in memory and not on the sheet. The entire sheet is loaded into a variable and then the work is done on the variable before the result (the variable) is written back to the sheet. So, if you have a speed problem then you might want to consider using this solution.

这篇关于Excel - VBA填充第一和最后一个值之间的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！