如何从第一个非黑色到最后一个非空白单元格（VBA）选择单元格范围？

Question

我试图从Excel工作簿中导出表格到管道分隔的txt文件，这些文件以与相应工作表相同的方式命名。问题是我无法让我的宏遍历工作簿中的不同工作表以将所有非空白单元格导出到txt文件。以下是我的代码：

Sub TableExtract() 

Dim myFile As String, WS_Count As Integer, x As Integer, rng As Range, cellValue As Variant, i As Integer, j As Integer 

WS_Count = ActiveWorkbook.Worksheets.Count 
For x = 1 To WS_Count 

    myFile = "C:\Users\mohamednuri.beitelma\Desktop\" & ActiveSheet.Name & ".txt" 

    Set rng = Sheets(x).Range("A1").CurrentRegion 
    Open myFile For Output As #1 

    For i = 1 To rng.Rows.Count 
     For j = 1 To rng.Columns.Count 

      cellValue = rng.Cells(i, j).Value 

      If j = rng.Columns.Count Then 
       Print #1, cellValue 
      Else 
       Print #1, cellValue & "|", 
      End If 

      Next j 
     Next i 
    Close #1 
Next x 

End Sub 

此代码返回错误。任何想法如何在第一个和最后一个非空白单元格之间的范围内选择内容并将其导出？

Answer 1

使用当前的区域属性：

Set rng = Range("A1").CurrentRegion 

这是选择A1的equivelant并按下Ctrl键+因为你分配正在引起

---

你的错误一个行号和一个列号到Range方法，你应该有一个地址或一个开始/结束单元格：

'// lets assume row = 5 
row = Range("A" & Rows.Count).End(xlUp).row 

'// lets assume col = 10 
col = Cells(1, Cells.Columns.Count).End(xlToLeft).Column 

'// this will produce Range(5, 10) <~~ invalid syntax 
Range(row, col).Select 

'// consider instead: 
Set rng = Range(Cells(1, 1), Cells(row, col)) 
'// or use the .CurrentRegion property as mentioned above if there are no gaps in your data.