excel visual basic中是否有任何函数可以查找没有空白行的colunms范围

Question

我试图找到没有空行的列数，并将其写在文本文件上，我的代码是这样的，我做不到

Private Sub Button1_Click()
Dim filename As String, lineText As String
Dim myrng As Range, i, j
filename = ThisWorkbook.Path & "MuhBeyanname.txt"      ' sadece dosya adresi
Open filename For Output As #1
Set myrng = Range("A3", "AF5000")           
For i = 1 To myrng.Rows.Count
lineText = ""
For j = 1 To myrng.Columns.Count
lineText = IIf(j = 1, "", lineText & vbTab) & myrng.Cells(i, j)
Next j
If lineText <> "" Then
Print #1, Replace(Replace(lineText, ",", ""), ".", ",")
End If
Next i

Close #1 End Sub

myrange在代码中是静态的，但在没有空白行的列行上必须是动态的

Answer 1

您没有回答我的澄清问题，但如果您需要确定活动工作表的最后一行和最后一列，您使用的范围应该按照以下方式构建：

'your declarations ...
'...
Dim sh As Worksheet, lastCol As Long, lastRow As Long
Set sh = ActiveSheet
lastRow = sh.Range("A" & Rows.count).End(xlUp).Row 'column A:A must have so many rows as the rest of the worksheet
lastCol = sh.cells(1, Columns.count).End(xlToLeft).Column 'First row must have so many columns as the rest of the worksheet

Set myrng = sh.Range("A3", sh.cells(lastRow, lastCol))
'After that your code, starting with
fileName = ThisWorkbook.Path & "MuhBeyanname.txt"      ' sadece dosya adresi
Open fileName For Output As #1
'the rest of your code
'of course, without Set myrng = Range("A3", "AF5000") 
'...