VBA填写两个单元格之间的所有单元格 [英] VBA Fill out all cells between two cells

问题描述

我正在尝试写一些VBA代码，它将填充两个单元格之间的所有单元格与两个单元格的值。

这是我所拥有的：

我希望代码可以填写删除所有单元格之间的像这样：

所以，你可以看到我想让所有的单元格之间填充与两个角落单元格相同的值。

任何帮助都非常感谢！感谢提前。

解决方案

将其放在一个新的模块中，运行 test_DTodor （更多信息在这里 LastRow 和 LastCol ）：

  Option Explicit 
 
 Sub test_DTodor（）
 Dim wS As Worksheet 
 Dim LastRow As Double 
 Dim LastCol As Double 
 Dim i As Double 
 Dim j As Double 
 Dim k As Double 
 Dim RowVal As String 
 
设置wS = ThisWorkbook.Sheets（Sheet1）
 LastRow = LastRow_1（wS）
 LastCol = LastCol_1（wS）
 
对于i = 1 To LastRow 
对于j = 1到LastCol 
与wS 
如果.Cells（i，j）<> vbNullString然后
'找到的行的第一个值
 RowVal = .Cells（i，j）.Value 
k = 1 
'填充到该行的下一个值
 Do While j + k <= LastCol And .Cells（i，j + k）= vbNullString 
 .Cells（i，j + k）.Value = RowVal 
k = k + 1 
循环
'转到下一行
退出
 Else 
结束如果
结束与'wS 
下一步j 
下一步i 
 End Sub 
 
公共功能LastCol_1（wS As Worksheet）As Double 
带有wS 
如果Application.WorksheetFunction.CountA（.Cells）<> 0然后
 LastCol_1 = .Cells.Find（What：=*，_ 
 After：=。Range（A1），_ 
 Lookat：= xlPart，_ 
 LookIn：= xlFormulas，_ 
 SearchOrder：= xlByColumns，_ 
 SearchDirection：= xlPrevious，_ 
 MatchCase：= False）.Column 
 Else 
 LastCol_1 = 1 
结束如果
结束
结束功能
 
公共功能LastRow_1（wS作为工作表）作为双
带wS 
如果Application.WorksheetFunction .CountA（.Cells）< 0然后
 LastRow_1 = .Cells.Find（What：=*，_ 
 After：=。Range（A1），_ 
 Lookat：= xlPart，_ 
 LookIn：= xlFormulas，_ 
 SearchOrder：= xlByRows，_ 
 SearchDirection：= xlPrevious，_ 
 MatchCase：= False）.Row 
 Else 
 LastRow_1 = 1 
如果
结束
结束功能
  

I am currently trying to write some VBA code which will fill out all cells between two cells with the value of the two cells.

Here is what I have :

And I would like the code to fill out all cells in between like this:

So, as you can see I would like all the cells in between to be filled out with the same value as the two corner cells.

Any help is very much appreciated! Thanks in advance.

解决方案

Place this in a new module and run test_DTodor (more info here on LastRow and LastCol):

Option Explicit

Sub test_DTodor()
    Dim wS As Worksheet
    Dim LastRow As Double
    Dim LastCol As Double
    Dim i As Double
    Dim j As Double
    Dim k As Double
    Dim RowVal As String

    Set wS = ThisWorkbook.Sheets("Sheet1")
    LastRow = LastRow_1(wS)
    LastCol = LastCol_1(wS)

    For i = 1 To LastRow
        For j = 1 To LastCol
            With wS
                If .Cells(i, j) <> vbNullString Then
                    '1st value of the row found
                    RowVal = .Cells(i, j).Value
                    k = 1
                    'Fill until next value of that row
                    Do While j + k <= LastCol And .Cells(i, j + k) = vbNullString
                        .Cells(i, j + k).Value = RowVal
                        k = k + 1
                    Loop
                    'Go to next row
                    Exit For
                Else
                End If
            End With 'wS
        Next j
    Next i
End Sub

Public Function LastCol_1(wS As Worksheet) As Double
    With wS
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            LastCol_1 = .Cells.Find(What:="*", _
                                After:=.Range("A1"), _
                                Lookat:=xlPart, _
                                LookIn:=xlFormulas, _
                                SearchOrder:=xlByColumns, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False).Column
        Else
            LastCol_1 = 1
        End If
    End With
End Function

Public Function LastRow_1(wS As Worksheet) As Double
    With wS
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            LastRow_1 = .Cells.Find(What:="*", _
                                After:=.Range("A1"), _
                                Lookat:=xlPart, _
                                LookIn:=xlFormulas, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False).Row
        Else
            LastRow_1 = 1
        End If
    End With
End Function

这篇关于VBA填写两个单元格之间的所有单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！