| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 2007 Worksheet, Hidden Columns, .CSV Format Saves Hidden Column Da | Excel Discussion (Misc queries) | |||
| Copy and Paste with hidden columns remaining hidden | Excel Discussion (Misc queries) | |||
| Enable Macros by Code | Excel Discussion (Misc queries) | |||
| Formula or Code to keep Hidden Rows Hidden | Excel Worksheet Functions | |||
| Hidden Columns No Longer Hidden after Copying Worksheet? | Excel Discussion (Misc queries) | |||
Linear Mode
