Home 
Search 
Today's Posts 
#1




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




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




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




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




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




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




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




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




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




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 
Display Modes  


Similar Threads  
Thread  Forum  
Find first empty cell in column  Excel Discussion (Misc queries)  
How to: Find first empty cell in column  Excel Worksheet Functions  
Delete Rows with Empty Cells with empty column 1  Excel Programming  
Find a empty cell in next column  Excel Discussion (Misc queries)  
How to find next empty cell within a column?  Excel Programming 