Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 28th 09, 02:04 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2009
Posts: 19
Default find empty cells in a column then append row that empty cell is in

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

  #2   Report Post  
Old January 28th 09, 02:25 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,393
Default find empty cells in a column then append row that empty cell is in

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"vbnewbie" wrote in message
...
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



  #3   Report Post  
Old January 28th 09, 02:46 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2009
Posts: 19
Default find empty cells in a column then append row that empty cell i

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

"Bernard Liengme" wrote:

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"vbnewbie" wrote in message
...
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




  #4   Report Post  
Old January 28th 09, 03:00 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 7,247
Default find empty cells in a column then append row that empty cell i

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)


On Wed, 28 Jan 2009 06:46:09 -0800, vbnewbie
wrote:

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

"Bernard Liengme" wrote:

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"vbnewbie" wrote in message
...
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




  #5   Report Post  
Old January 28th 09, 03:08 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2009
Posts: 19
Default find empty cells in a column then append row that empty cell i

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

"Bernard Liengme" wrote:

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"vbnewbie" wrote in message
...
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






  #6   Report Post  
Old January 28th 09, 03:26 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2009
Posts: 19
Default find empty cells in a column then append row that empty cell i

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



"Chip Pearson" wrote:

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)


On Wed, 28 Jan 2009 06:46:09 -0800, vbnewbie
wrote:

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

"Bernard Liengme" wrote:

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"vbnewbie" wrote in message
...
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




  #7   Report Post  
Old January 28th 09, 03:49 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,393
Default find empty cells in a column then append row that empty cell i

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"vbnewbie" wrote in message
...
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

"Bernard Liengme" wrote:

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"vbnewbie" wrote in message
...
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






  #8   Report Post  
Old January 28th 09, 04:06 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default find empty cells in a column then append row that empty cell i

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

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

vbnewbie wrote:

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

"Bernard Liengme" wrote:

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"vbnewbie" wrote in message
...
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





--

Dave Peterson
  #9   Report Post  
Old January 28th 09, 04:57 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2009
Posts: 19
Default find empty cells in a column then append row that empty cell i

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



"Chip Pearson" wrote:

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)


On Wed, 28 Jan 2009 06:46:09 -0800, vbnewbie
wrote:

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

"Bernard Liengme" wrote:

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"vbnewbie" wrote in message
...
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




  #10   Report Post  
Old January 29th 09, 09:27 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2009
Posts: 19
Default find empty cells in a column then append row that empty cell i

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!

"Bernard Liengme" wrote:

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"vbnewbie" wrote in message
...
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

"Bernard Liengme" wrote:

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"vbnewbie" wrote in message
...
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








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find first empty cell in column TomHull Excel Discussion (Misc queries) 1 November 9th 09 05:16 AM
How to: Find first empty cell in column DW Excel Worksheet Functions 18 October 12th 07 05:57 AM
Delete Rows with Empty Cells with empty column 1 Scott Excel Programming 5 October 2nd 06 11:57 PM
Find a empty cell in next column Michael Excel Discussion (Misc queries) 3 June 15th 05 02:18 PM
How to find next empty cell within a column? Rick Excel Programming 5 May 27th 05 07:25 PM


All times are GMT +1. The time now is 11:19 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017