find empty cells in a column then append row that empty cell is in

V

vbnewbie

I am trying to read all empty cells in a column then append the rows that the
cells are in to a text file using VB6
Microsoft EXCEL 2003
 
B

Bernard Liengme

I am assuming: (1) the column is A, and (2) you want to start at row 1.
This subroutine looks at column A on Sheet2 and on Sheet3 in column A it
lists the row number of every empty cell. The Sheet3 could be copied to a
text file or saved as a TXT file.
best wishes

Sub ListEmpty()
Sheets("Sheet2").Activate
lastcell = Cells(Cells.Rows.Count, "A").End(xlUp).Row
For j = 1 To lastcell
If Cells(j, 1) = "" Then
k = k + 1
Sheets("Sheet3").Cells(k, 1) = j
End If
Next j
End Sub
 
V

vbnewbie

Thanks for this Bernard it's a good start but I need to display the whole
contents of the row, not just the row numbers, also in a text file rather
than on another sheet.

Hope you can help, or anyone else out there?

Cheers
 
C

Chip Pearson

Try the following code:

Sub AAA()
Dim FName As Variant
Dim FNum As Integer
Dim LastRow As Long
Dim R As Range
Dim C As Range
Dim S As String
Dim WS As Worksheet
Dim StartRow As Long

FName = Application.GetSaveAsFilename(vbNullString, _
"Text Files (*.txt),*.txt")
If FName = False Then
' user cancelled
Exit Sub
End If
FNum = FreeFile
Open FName For Output Access Write As #FNum
Set WS = ActiveSheet
StartRow = 1
With WS
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Set R = WS.Cells(StartRow, "A")
Do Until R.Value = vbNullString
Set C = R.EntireRow.Cells(1, "A")
S = vbNullString
Do Until C.Value = vbNullString
S = S & C.Text & " "
Set C = C(1, 2)
Loop
S = Trim(S)
Print #FNum, S
Set R = R(2, 1)
Loop
Close #FNum
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
V

vbnewbie

Hello again

I am submitting this from a macro and get the following error

" RunTime error 9 subscript out of range"

And when I open the debugger the line

Sheets("Sheet2").Activate

is in error

Cheers
 
V

vbnewbie

Hi Chip

This doesn't work, I get an empty file.

More details - my empty cells are in column G , which lines should I change
to make this work?

Cheers
 
B

Bernard Liengme

This look for blanks in column G and copyies entrie row to Sheet3

Sub ListEmpty()
Sheets("Sheet2").Activate
lastcell = Cells(Cells.Rows.Count, "A").End(xlUp).Row
For j = 1 To lastcell
If Cells(j, 7) = "" Then
k = k + 1
Rows(j & ":" & j).Copy Sheets("Sheet3").Cells(k, 1)
End If
Next j
End Sub

best wsihes
 
D

Dave Peterson

That means you don't have a worksheet named Sheet2.

Change the code or change the name of the sheet on the worksheet tab.
 
V

vbnewbie

Hi Chip

Almost there, the column I need to search on is column G, there are empty
cells in other columns, which I need to ignore, searching specifically on
column G only for empty cells. Which lines of the code would I need to alter
to acheive the results?

Thanks for all your help so far
 
V

vbnewbie

Hi Bernard
I have solved the problem I had with this I had two shets called 2Orders"
and "Results - my final code reads -
Sub Rectangle7_Click()

orders.Activate
lastcell = Cells(Cells.Rows.Count, "A").End(xlUp).Row
For j = 1 To lastcell
If Cells(j, 7) = "" Then
k = k + 1
Rows(j & ":" & j).Copy Sheets("Results").Cells(k, 1)
End If
Next j

End Sub

Many Many thanks for all your help!
 
Top