enable re-code to re-read on re-hidden columns, if can be possible

D

driller

Hello again,

I have this so good macro that function very quick.
My problem is that I like the results to be hidden to avoid unnoticeable
changes.

When I ran the macro without hiding any columns, the results are perfect.

When I hide cols. A,B,C & D.
I don't realize that repetitive data keeps building (Col A, B, C & D) up
everytime I click the macro - which should not work like this as intended.
Maybe I had made errors during the copy-paste of the code.

please help..
herebelow is the re-code again
--
Sub GetFileDetails()
Dim fso As Object, folder As Object
Dim lngRow As Long, ws As Worksheet
Set fso = CreateObject("Scripting.FileSystemObject")

For Each ws In Worksheets
ws.Range("D1").Resize(ws.Cells(Rows.Count, _
"A").End(xlUp).Row).Value = "Not found"
ws.Range("D1") = "Status"
If fso.FolderExists(ws.Range("A1")) Then
Set folder = fso.GetFolder(ws.Range("A1"))
lngRow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
For Each fl In folder.Files
Set rngFound = ws.Range("A:A").Find(fl.Name, LookAt:=xlPart)
If rngFound Is Nothing Then
ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _
folder.Path & "\" & fl.Name & """,""" & fl.Name & """)"
ws.Range("B" & lngRow) = fl.Size
ws.Range("C" & lngRow) = fl.DateLastModified
ws.Range("D" & lngRow) = "New"
lngRow = lngRow + 1
Else
If ws.Range("B" & rngFound.Row) = fl.Size And _
ws.Range("C" & rngFound.Row) = fl.DateLastModified Then
ws.Range("D" & rngFound.Row) = "No change"
Else
ws.Range("D" & rngFound.Row) = "Modified"
End If
End If
Next

End If
Next
End Sub
 
P

Per Jessen

Hi

You can turn off screen updating, unhide columns, run your code and set
every thing back afterwards, like this:

Sub ccc()
Application.ScreenUpdating = False
Columns("A:D").Hidden = False

'Your Code

Columns("A:D").Hidden = True
Application.ScreenUpdating = True
End Sub

Regards,
Per
 
J

Jacob Skaria

Modified to suit...

Sub GetFileDetails()
'Jacob Skaria: 10 Oct 2009
Dim fso As Object, folder As Object,rngFound As Range
Dim lngRow As Long, ws As Worksheet
Set fso = CreateObject("Scripting.FileSystemObject")

For Each ws In Worksheets
ws.Columns("A:D").Hidden = False
ws.Range("D1").Resize(ws.Cells(Rows.Count, _
"A").End(xlUp).Row).Value = "Not found"
ws.Range("D1") = "Status"
If fso.FolderExists(ws.Range("A1")) Then
Set folder = fso.GetFolder(ws.Range("A1"))
lngRow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
For Each fl In folder.Files
Set rngFound = Range("A:A").Find(fl.Name, LookAt:=xlPart)
If rngFound Is Nothing Then
ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _
folder.Path & "\" & fl.Name & """,""" & fl.Name & """)"
ws.Range("B" & lngRow) = fl.Size
ws.Range("C" & lngRow) = fl.DateLastModified
ws.Range("D" & lngRow) = "New"
lngRow = lngRow + 1
Else
If ws.Range("B" & rngFound.Row) = fl.Size And _
ws.Range("C" & rngFound.Row) = fl.DateLastModified Then
ws.Range("D" & rngFound.Row) = "No change"
Else
ws.Range("D" & rngFound.Row) = "Modified"
End If
End If
Next
End If
ws.Columns("A:D").Hidden = False
Next
End Sub

If this post helps click Yes
 
D

driller

thanks,

it works now even when i prompt the macro to run for all sheet
simultaneously wherein some sheets have hidden columns.

I think I need to restore back the original view of the sheet, after running
the code (with hidden columns only for somewhere - not all).

Is this possible ?
--
Sub GetFileDetails()
'Jacob Skaria: 11 Oct 2009
Dim fso As Object, folder As Object, rngFound As Range
Dim lngRow As Long, ws As Worksheet
Set fso = CreateObject("Scripting.FileSystemObject")

For Each ws In Worksheets
ws.Columns("A:D").Hidden = False
ws.Range("D1").Resize(ws.Cells(Rows.Count, _
"A").End(xlUp).Row).Value = "Not found"
ws.Range("D1") = "Status"
If fso.FolderExists(ws.Range("A1")) Then
Set folder = fso.GetFolder(ws.Range("A1"))
lngRow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
For Each fl In folder.Files
Set rngFound = ws.Range("A:A").Find(fl.Name, LookAt:=xlPart)
If rngFound Is Nothing Then
ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _
folder.Path & "\" & fl.Name & """,""" & fl.Name & """)"
ws.Range("B" & lngRow) = fl.Size
ws.Range("C" & lngRow) = fl.DateLastModified
ws.Range("D" & lngRow) = "New"
lngRow = lngRow + 1
Else
If ws.Range("B" & rngFound.Row) = fl.Size And _
ws.Range("C" & rngFound.Row) = fl.DateLastModified Then
ws.Range("D" & rngFound.Row) = "No change"
Else
ws.Range("D" & rngFound.Row) = "Modified"
End If
End If
Next
End If
ws.Columns("A:D").Hidden = False
Next
End Sub
 
D

driller

thanks,

it works now even when i prompt the macro to run for all sheet
simultaneously wherein some sheets have hidden columns.

I think I need to restore back the original view of the sheet, after running
the code (with hidden columns only for somewhere - not all).

Is this possible ?
--
Sub GetFileDetails()
'Jacob Skaria: 11 Oct 2009
Dim fso As Object, folder As Object, rngFound As Range
Dim lngRow As Long, ws As Worksheet
Set fso = CreateObject("Scripting.FileSystemObject")

For Each ws In Worksheets
ws.Columns("A:D").Hidden = False
ws.Range("D1").Resize(ws.Cells(Rows.Count, _
"A").End(xlUp).Row).Value = "Not found"
ws.Range("D1") = "Status"
If fso.FolderExists(ws.Range("A1")) Then
Set folder = fso.GetFolder(ws.Range("A1"))
lngRow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
For Each fl In folder.Files
Set rngFound = ws.Range("A:A").Find(fl.Name, LookAt:=xlPart)
If rngFound Is Nothing Then
ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _
folder.Path & "\" & fl.Name & """,""" & fl.Name & """)"
ws.Range("B" & lngRow) = fl.Size
ws.Range("C" & lngRow) = fl.DateLastModified
ws.Range("D" & lngRow) = "New"
lngRow = lngRow + 1
Else
If ws.Range("B" & rngFound.Row) = fl.Size And _
ws.Range("C" & rngFound.Row) = fl.DateLastModified Then
ws.Range("D" & rngFound.Row) = "No change"
Else
ws.Range("D" & rngFound.Row) = "Modified"
End If
End If
Next
End If
ws.Columns("A:D").Hidden = False
Next
End Sub
 
J

Jacob Skaria

'If you are looking at running this for all sheets and return the hidden
status of the columns to the original state (hidden or nonhidden then)

Dim blnHidden as boolean
For Each ws In Worksheets
blnHidden = ws.Columns(1).Hidden
ws.Columns("A:D").Hidden = False
'the rest of the code remains same
ws.Columns("A:D").Hidden = blnHidden
Next

OR If you are looking at running this for sheets which are not hidden then

For Each ws In Worksheets
If ws.Columns(1).Hidden = False Then
'the rest of the code remains same
End If
Next

'Or If you are looking at running this for all sheets with columns hidden

For Each ws In Worksheets
ws.Columns("A:D").Hidden = False
'the rest of the code remains same
ws.Columns("A:D").Hidden = True
Next


If this post helps click Yes
 
D

driller

thanks again

--
regards

Jacob Skaria said:
'If you are looking at running this for all sheets and return the hidden
status of the columns to the original state (hidden or nonhidden then)

Dim blnHidden as boolean
For Each ws In Worksheets
blnHidden = ws.Columns(1).Hidden
ws.Columns("A:D").Hidden = False
'the rest of the code remains same
ws.Columns("A:D").Hidden = blnHidden
Next

OR If you are looking at running this for sheets which are not hidden then

For Each ws In Worksheets
If ws.Columns(1).Hidden = False Then
'the rest of the code remains same
End If
Next

'Or If you are looking at running this for all sheets with columns hidden

For Each ws In Worksheets
ws.Columns("A:D").Hidden = False
'the rest of the code remains same
ws.Columns("A:D").Hidden = True
Next


If this post helps click Yes
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

a macro with finishing criteria 2
macro function--very nice 7
hyperlink solution requested 1
Macro Help CONT 2003 1
Removing empty rows issue Please Help! 4
My Macro is slower than the turtle 1
How to get each table's first line of record and write into excel? 3
userform stopping duplication 12

Top