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

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - enable re-code to re-read on re-hidden columns, if can be possible (https://www.excelbanter.com/excel-discussion-misc-queries/245174-enable-re-code-re-read-re-hidden-columns-if-can-possible.html)

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

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
--
thanks for any suggestion.
--
regards,

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

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

"driller" skrev i meddelelsen
...
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
--
thanks for any suggestion.
--
regards,

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

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
---------------
Jacob Skaria

"driller" wrote:

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
--
thanks for any suggestion.
--
regards,

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

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
---

--
regards

"Per Jessen" wrote:

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

"driller" skrev i meddelelsen
...
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
--
thanks for any suggestion.
--
regards,

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

'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
---------------
Jacob Skaria

"driller" wrote:

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
---

--
regards

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

thanks again

--
regards

"Jacob Skaria" wrote:

'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
---------------
Jacob Skaria

"driller" wrote:

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
---

--
regards