Web Development·12,091 discussions

VAB problem in Excell:

Locked

I have a worksheet that is importing data from a variable length text file. after the import I need to append another line of data from another worksheet directly at the end of the list. My problem is that blank lines are appearing at the bottom ofthe first import. I need to delete these lines. I found some code to delete blank lines but it does not work. I beleive this is because the values in the cells are nulls (not blank)?. If I run the first import delete the blank lines at the bottom of the worksheet I imported into and then run the second part that appends the trailer to the bottom it works fine.
can any one point me in the right direction so I can finish this project?

Topic

VAB problem in Excell:

In reply to VAB problem in Excell:

What you might be able to do via programming is:
1) find out how many rows of data you have and put that in a var
2) execute a do loop that starts at the last line and goes to the first. In the loop you would process a cell that you know has to have a value in each row(eg primary key for the row)and see if it is null. Then when it hits a non null value it exits the do loop.

Example
var = ExcelObject.Rows.Count

do until var = 0
tempstring = “A” & var ‘set which row you ‘are on with theColumn A
Set NullCell = Worksheets(1).Range(tempstring)
nullcheck = isnull(nullcell.value)
if nullcheck = true
nullcell.Entirerow.Delete
var = var – 1
else
exit do
endif
loop

not the best code to do this but it should work

VAB problem in Excell:

In reply to VAB problem in Excell:

Thanks for your input, it did not work in my instance but I will still award you with half the points.
The code that did work follows:
Sub DeleteEmptyRows()
Sheets(“temp”).Select
LastRow = ActiveSheet.UsedRange.Row – 1 + ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(r)) = 0 _
Then Rows(r).delete
Next r
End Sub

VAB problem in Excell:

In reply to VAB problem in Excell:

Assuming that there is one column that will always have data (in this case Column A):

Sub Test()
Dim i As Long
i = 1
Do While Len(Range(“A” & i).Text) > 0
i = i + 1
Loop
‘i is the first empty row – remove these rows
Rows(i & “:” & i + 10).Delete Shift:=xlUp

‘Now Copy Value from another sheet
End Sub

I think that this is what you are looking for. If not, please let me know. This problem should be an easy one to solve.

VAB problem in Excell:

In reply to VAB problem in Excell:

Thanks for your input, it did not work in my instance but I will still award you with half the points.
The code that did work follows:
Sub DeleteEmptyRows()
Sheets(“temp”).Select
LastRow = ActiveSheet.UsedRange.Row – 1 + ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(r)) = 0 _
Then Rows(r).delete
Next r
End Sub

[Author]

Replies