Data Clean

[Manuel998]

Hi,

I have some data in raw format that needs to be cleaned so that the blank fields are removed, i have tried to use the got to special >blanks, to delete the blank rows but the field then go out of sync. Can i please kindly ask if you could help me with a macro to remove the blank fields without the data going out of sync when i delete the rows.

The Data is in the data tab and the format of the report is in the Headcount Tab. Thanks in advance for any help, it would be great if you can help as i got 250 sheets to go through.

Thanks
Manuel

 

[vletm]

@Manuel998
Quickly looks that those can 'just' sort with 'Surname' & 'Forename 1' ...
if so ... why need macro?
or do You have to do this hourly?

 

[Manuel998]

The task is done weekly but obviously what i need to do is get rid of the blanks and ensure that the fte aligns. When i get rid of the blanks the fte falls out of sync.

 

[vletm]

... something challenge with this?

 

[Manuel998]

Great! how did you do this Vletm?

 

[Manuel998]

Actually i worked it out thanks Vlet you are a star

 

[Manuel998]

sorry Vlet if i have around 250 workbooks can i actually record a macro to sort and loop through all the workbooks?

 

[Manuel998]

the 250 workbooks are in a folder

 

[vletm]

Of course You could record that macro too ...
but 250 times same routine ... sounds 'great'!
a) really gotta do that weekly?
b) why?
c) If really need to that kind of 'thing'
then it would be 'wise' to find sample of loop for that.
I won't do that kind of 'thing' without really good reason!

 

[Marc L]

Hi !

Easy and instant way is using Excel basics features : a formula,
a sort, search first row to delete and clear until the end at once !

A demonstration according to initial attachment :​

Sub Demo()
           Dim Rg As Range, C&, L&
     Application.ScreenUpdating = False
With Worksheets("Data")
          C = .UsedRange.Columns.Count + 1
          L = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    With .Range("A4:A" & L).Resize(, C)
         .Columns(C).Formula = "=(A4=""Personnel Number"")+(A4="""")"
         .Sort .Cells(C), xlAscending, Header:=xlNo
           Set Rg = .Columns(C).Find(1, , xlValues)
        If Not Rg Is Nothing Then
         .Rows(Rg.Row & ":" & L).Clear
           Set Rg = Nothing
        End If
         .Columns(C).Clear
    End With
End With
     Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[vletm]

@Marc L
only sorting as I wrote is enough, no need to clear
'the bigger' thing is that there are 250 workbooks in one folder,
I won't do that kind of loops

 

[Marc L]

Sorting is not enough, see your result worksheet rows #1318 to 1372 …
As it depends also on what result OP wants.

My code can easily be changed to loop worksheets if it's exactly same layout.

But to just extract data to Headcount worksheet
using Excel basics aka an advanced filter
it is very not necessary to mod any source data worksheet ‼
​

 

[Manuel998]

Hi Marc & Vlet,

Thank you so much for the help one last question, i have managed to get the loop but the problem i am having is each file in the folder is password protected with a password and i have to type the password each time the loop runs and also have to save each file individually can i please ask if you could guide me as to how i can tweak my macro to achieve this? (Macro below)


Sub LoopThroughFiles()

FolderName = "C:\Users\mrodrigues\Desktop\NPG\Payroll\"
If Right(FolderName, 1) <> Application.PathSeparator Then FolderName = FolderName & Application.PathSeparator
Fname = Dir(FolderName & "*.xls")

'loop through the files
Do While Len(Fname)

With Workbooks.Open(FolderName & Fname)

' here comes the code for the operations on every file the code finds
Columns("A:Z").Select
ActiveSheet.DrawingObjects.Select
Selection.Delete
Dim Rg As Range, C&, L&
Application.ScreenUpdating = False
With Worksheets("Report")
C = .UsedRange.Columns.Count + 1
L = .UsedRange.Rows(.UsedRange.Rows.Count).Row
With .Range("A4:A" & L).Resize(, C)
.Columns(C).Formula = "=(A4=""Personnel Number"")+(A4="""")"
.Sort .Cells(C), xlAscending, Header:=xlNo
Set Rg = .Columns(C).Find(1, , xlValues)
If Not Rg Is Nothing Then
.Rows(Rg.Row & ":" & L).Clear
Set Rg = Nothing
End If
.Columns(C).Clear
End With
End With
Application.ScreenUpdating = True
End With

' go to the next file in the folder
Fname = Dir

Loop

End Sub

 

[vletm]

@Marc L - merci
... I read and done just as wanted 'the blank fields are removed'
but of course those rows maybe no need as You wrote
...
and You would continue with loop & password - merci