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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 11:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com