exporting static range without empty rows

[AnthonyMinnaar]

Hi folks!

i'm making a post for CNC machine (fanuc control). but the problem is, that when I export to a .TAP file, the machine reads the blank rows, and inserts a ";". which isn't that much of a disaster, but I'd like to make the posted programs as clean and clear as possible.

I'm currently using this code

Sub POST()
Sheets("PROGRAMMA").Select

Columns.EntireColumn.Hidden = False
Rows.EntireRow.Hidden = False

For Each c In Range("A2:A70")
If c.Value = "" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next

Dim wb As Workbook
Dim saveFile As String
Dim WorkRng As Range
On Error Resume Next
xTitleId = "SELECTIE"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("SELECTIE", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = Application.Workbooks.Add
WorkRng.Copy
wb.Worksheets(1).Paste
saveFile = Application.GetSaveAsFilename(fileFilter:="TAP (*.TAP), *.TAP")
wb.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False
wb.Close
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

What should I change in here, so that it always uses a static range (e.g. A1 to G70), and not having to constantly select what range is to be exported. also, this code also still exports the hidden rows, is there a workaround so it only exports the visible rows within the static range?

as you probably can tell, I'm quite a rookie when it comes to VBA, so my apologies if this thread is unclear.


with kindest regards,

Anthony

 

[mart37]

Replace: WorkRng.Copy
with

WorkRng.SpecialCells(xlCellTypeVisible).Copy

 

[AnthonyMinnaar]

Replace: WorkRng.Copy
with

WorkRng.SpecialCells(xlCellTypeVisible).Copy

thanks a tonne, Mart!