Solved: Delete empty rows and copy to another worksheet

[CodeMakr]

I've got about 2K row of data inported from .txt format (so there are empty rows throughout). I need to delete the empty rows and then copy the contguous data to another spreadsheet. I've got this code, but am wondering if it is the most affective way to do that?

[VBA]
Option Explicit
Sub DeleteEmptyRows()
Dim i As Long
Application.ScreenUpdating = False
For i = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Application.WorksheetFunction.CountA(Sheets(1).Cells(i, 1).EntireRow) = 0 Then
Sheets(1).Cells(i, 1).EntireRow.Delete
End If
Next
i = 0
For i = 1 To Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
If Not Sheets(1).Cells(i, 1).EntireRow Is Nothing Then
Sheets(1).Cells(i, 1).EntireRow.Copy Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1)
End If
Next

Application.ScreenUpdating = True
End Sub
[/VBA]

[Zack Barresse]

Hey Mark,

Define "blank rows". Is that, where there is no data in the row? Is it when there is no data in column A of each row? We can do either without looping.

[CodeMakr]

Between the data, some of the entire row is blank when it comes in, so the 2K rows of data are spread out over about 3K+ of actual space on the sheet.

[Zack Barresse]

Headers on row 1 with data on row 2?

[CodeMakr]

Hey Zack,

No headers on the text file, only the data. I open the text files (access reports exported as text format) to retrieve the data and append into another sheet, then close the text file without saving. The headers are on the sheet the text file data is getting copied to, which is why I put the Offset on the copy.

[Zack Barresse]

Hmm, why even come out of Access? Anyway, this might get you what you want...

[vba]Sub DeleteBlanksAndCopyNons()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rngFormula As Range, strLastCol As String
Set ws1 = Sheets(1)
Set ws2 = Sheets(2)
'Delete wholly blank rows
ws1.Columns(1).Insert
Set rngFormula = ws1.Range("A2:A" & ws1.Cells(ws1.Rows.Count, "B").End(xlUp).Row)
strLastCol = ws1.Cells(2, ws1.Columns.Count).Address(0, 0)
rngFormula.Formula = "=COUNTA(B2:" & strLastCol & ")"
ws1.Columns(1).AutoFilter 1, 0
rngFormula.SpecialCells(xlCellTypeVisible).EntireRow.Delete
ws1.Columns(1).Delete
ws1.AutoFilterMode = False
'Copy the other values to next sheet
ws1.Range("A2:F" & ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row).Copy ws1.Cells(1, 1)
Application.CutCopyMode = False
End Sub[/vba]

Try it on test data first. Also, ensure the ranges are correct. I guessed that column F is the last column, change as necessary.

[CodeMakr]

Thanks Zack. I have to go through access to obtain the data off our server (TN). There is probably a way to do that via VBA, but that is WAY over my head

I tried your code, and at first it didn't work (deleted the rows but didn't copy). Then I noticed your copy sequence was from Ws1 to Ws1. I changed Ws1 to Ws2 and it worked great.

Thanks for your time

[Zack Barresse]

Ah, thanks for the catch! Glad it worked for you after you cleaned up my mess.

You should start another thread about obtaining data from your server.

[CBrine]

CodeMakr,
You most likely could have revised the access report SQL, and added a condition <>"" in one of the columns to eliminate it at the source. Let me know if you are interested in understanding how to do this? or if it's just a one time thing for you.

Cal

[CodeMakr]

Cal,
In the access report there are no blank rows. They only appear in the text file I export (i.e., you don't see them in printed form, only when you open the text file in excel). Not sure why this happens? I guess my question is.....is this an access export/setup issue or an excel import/load issue?

[Zack Barresse]

Can you export from Access to Excel instead of an intermediary text file? Or how about both?

[CBrine]

The report must be based on a query in the query screen. The report most likely formats the data into a presentable manor, but this format does not translate to excel very well, which is most likely why the data is messed up in excel. I would suggest you find the underlying query and do an export of that.

HTH
Cal

[CodeMakr]

I am connecting via terminal server to the database, and there is no excel. The lag is pretty bad, so the export in .txt format turned out to be the fastest way to go. I'll try exporting the query and see what it gives me.

Thanks for all the help Zack/Cal