Delete empty rows after importing new data to new sheet.

flay

New Member
Joined
Feb 9, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Hey,
i have a big 130,000 row data base which i pull on daily bases.
i have created a script which lets you :
1.pick the file you want
2.copies it using .CurrentRegion funciton
3.pastes it to a new sheet in my workbook.
i want to add that new file as a new sheet to my current workbook, when i copy and paste it it comes acorss some empty rows in the data base which does not allow me to copy the whole SHEET i just imported..
i have creatd another script which goes trough the file and deltes all empty rows so it will be continuous and then i will be able to copy the whole data.
but i dont know where to place the CALL command for it to delete the rows before it copies it.

(IMPORTS THE FILE)
'''''''''''''''''''''''''''''''''''''
Sub import()
Dim textfile As Workbook
Dim openfiles As Variant
Dim i As Integer

openfiles = Application.GetOpenFilename(Title:="Select file(s) to import", MultiSelect:=True)

call blank(where do i call the function for it to delete the rows before coping it .)


For i = 1 To Application.CountA(openfiles)
Set textfile = Workbooks.Open(openfiles(i))
Call blank
textfile.Sheets(1).Range("A1").CurrentRegion.Copy
Workbooks(1).Worksheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
textfile.Close
Application.ScreenUpdating = False
Next i

End Sub

'''''''''''''''''''''''
(DELETES EMPTY ROWS)
''''''''''''''''''''''''''''
Sub blank()

Dim x As Long

With ActiveSheet

For x = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
If WorksheetFunction.CountA(.Rows(x)) = 0 Then
ActiveSheet.Rows(x).Delete
End If
Next

End With

End Sub
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,176,002
Messages
5,900,799
Members
434,854
Latest member
ExcelMuffin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top