使用VBA识别特定范围内的第一个空列 [英] Identify the first emtpy column within a certain range using VBA

问题描述

我有以下Excel电子表格:

I have the following Excel spreadsheet:

       A                 B         C         D        E          F         G        H
1                     2019-03   2019-04   2019-05   2019-06   2019-07   Total
2    
3   Revenue             500       600                                  1.100
4   COGS                -40       -30                                    -70
5   Gross Profit        460       570        0        0        0       1.030
6   
7   OPEX                -10       -12                                    -22
8   Interests           -20       -50                                    -70
9
8   EBT                 430       508        0        0        0         938

公式:

Row 5 = SUMS of Row 3 and Row 4
Row 8 = SUMS of Row 5, Row 7 and Row 8
Column G = SUMS of Columns B to H

电子表格显示公司每月的表现.

The spreadsheet shows the performance of a company per month.

每次达到新的月份时，我都希望将上个月的值复制到新的月份.因此，我需要确定第一个空列.我试图从此处a>:

Each time a new month is reached I want to copy the values from the last month to the new month. Therefore, I need to identify the first empty column. I tried to go with the VBA solution from here:

Sub Last_Used_Column()
c = Sheet1.Cells(3, Columns.Count).End(xlToLeft).Column
MsgBox c
End Sub

现在的问题是，与相比，这个问题，我有一个新的 G列，其中显示了总体效果.因此，使用上述代码，我将得到 Colum H ，但我想将 D列识别为第一个空列.

The issue is now, that in contrast to this question I have a new Column G which displays the total performance. Therefore, with the above code I will get Colum H as result but I want to have Column D to be identified as the first empty column.

我认为一种解决方案可能是将VBA查找第一个空列的范围限制为 Columns B:F .

I think a solution might be to limit the range in which the VBA should look for the first empty column to Columns B:F.

我该如何更改上面的代码以实现此目的?

How do I have to change the above code to achieve this?

推荐答案

使用此

Dim c As Long
c = Sheet1.Cells(3, Sheet1.Columns.Count).End(xlToLeft).Offset(ColumnOffset:=-1).End(xlToLeft).Column 

它使用这个

c = Sheet1.Cells(3, Sheet1.Columns.Count).End(xlToLeft) 

查找最后使用的列(在您的示例G中).然后它向左移一列

to find the last used column (in your example G). Then it goes one column left

.Offset(ColumnOffset:=-1)

并再次找到最后使用的列(从此处开始)

and finds again the last used column (starting from there)

.End(xlToLeft).Column

最后，您可以使用 c + 1 将空列移至最后使用的列的右侧.

finally you can use c + 1 to get the empty column right of the last used column.

注意:如果没有空列，则 c 将为 1 ，因此您可以轻松地对此进行测试:

Note: If there are no empty columns then c will be 1 so you can easily test this:

If c = 1 Then
    MsgBox "No empty columns found", vbCritical
    Exit Sub
End If

这篇关于使用VBA识别特定范围内的第一个空列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！