Regouin
02-21-2005, 01:21 AM
Hello everyone,
I am new to all this, but please be kind enough to help me.
I am trying to get a sheet to only display the data that is of any importance. When i dont want the row to show the first cell is empty. Otherwise it shows text.
What i have accomplished thus far by snooping around the board is making the rows dissapear when the first row is zero, but i cant get it to select the rows when their blank, most attempts just hid all the rows.
Sub HideRows()
Dim ws As Worksheet
Application.ScreenUpdating = False
On Error Resume Next
For Each ws In ThisWorkbook.Worksheets
With ws
.AutoFilterMode = False
With .Range("a1")
.EntireRow.Hidden = _
(CBool(Len(.Value)) And _
.Value = 0)
End With
Range(.Cells(1, 1), .Cells(65336, 1).End(xlUp)) _
.AutoFilter Field:=1, Criteria1:="<>0", _
visibleDropDown:=False
End With
Next
Application.ScreenUpdating = True
End Sub
then after that i want excel to export just the displayed data into either a text or a new sheet, thus far i have managed to export all the data (including the hidden lines) into a text file.
Sub createtext()
Set fsoObj = CreateObject("Scripting.FileSystemObject")
Dim Fs As Object
Dim strPath As String
Dim strFileMask As String
Dim f As String
Dim stKallFil As String
Set Fs = CreateObject("Scripting.FileSystemObject")
If MsgBox(strExcelApp & "Onderhoudsgegevens versturen?", _
vbYesNo + vbQuestion) = vbNo Then
Exit Sub
End If
Application.DisplayAlerts = False
Application.EnableEvents = False
With fsoObj
If .FolderExists("C:\tempmail\") Then
Else
.CreateFolder ("C:\tempmail")
End If
Application.ScreenUpdating = False
Sheets("hoofd").Select
Sheets("hoofd").Copy
ActiveWorkbook.SaveAs Filename:="C:\tempmail\onderhoud.xls", _
FileFormat:=xlText, CreateBackup:=False
ActiveWindow.Close
On Error Resume Next
'Kill "C:\tempmail\*.*"
'RmDir "C:\tempmail"
End With
Set fsoObj = Nothing
End Sub
then after that i want it to send an email out with the txt file attached, but I'll get to that later, first i want this to work.
TIA Frank
I am new to all this, but please be kind enough to help me.
I am trying to get a sheet to only display the data that is of any importance. When i dont want the row to show the first cell is empty. Otherwise it shows text.
What i have accomplished thus far by snooping around the board is making the rows dissapear when the first row is zero, but i cant get it to select the rows when their blank, most attempts just hid all the rows.
Sub HideRows()
Dim ws As Worksheet
Application.ScreenUpdating = False
On Error Resume Next
For Each ws In ThisWorkbook.Worksheets
With ws
.AutoFilterMode = False
With .Range("a1")
.EntireRow.Hidden = _
(CBool(Len(.Value)) And _
.Value = 0)
End With
Range(.Cells(1, 1), .Cells(65336, 1).End(xlUp)) _
.AutoFilter Field:=1, Criteria1:="<>0", _
visibleDropDown:=False
End With
Next
Application.ScreenUpdating = True
End Sub
then after that i want excel to export just the displayed data into either a text or a new sheet, thus far i have managed to export all the data (including the hidden lines) into a text file.
Sub createtext()
Set fsoObj = CreateObject("Scripting.FileSystemObject")
Dim Fs As Object
Dim strPath As String
Dim strFileMask As String
Dim f As String
Dim stKallFil As String
Set Fs = CreateObject("Scripting.FileSystemObject")
If MsgBox(strExcelApp & "Onderhoudsgegevens versturen?", _
vbYesNo + vbQuestion) = vbNo Then
Exit Sub
End If
Application.DisplayAlerts = False
Application.EnableEvents = False
With fsoObj
If .FolderExists("C:\tempmail\") Then
Else
.CreateFolder ("C:\tempmail")
End If
Application.ScreenUpdating = False
Sheets("hoofd").Select
Sheets("hoofd").Copy
ActiveWorkbook.SaveAs Filename:="C:\tempmail\onderhoud.xls", _
FileFormat:=xlText, CreateBackup:=False
ActiveWindow.Close
On Error Resume Next
'Kill "C:\tempmail\*.*"
'RmDir "C:\tempmail"
End With
Set fsoObj = Nothing
End Sub
then after that i want it to send an email out with the txt file attached, but I'll get to that later, first i want this to work.
TIA Frank