Can’t hide column using VBA

[shironokuro]

Hello all,
Firstly I’m not that good in English so I’m sorry if my grammar is hard to understand.

my macro can get unique value in column “D” and split to each workbook by copy row that unique value was (example : in column D at row 1,5,7 is got same data so my macro will copy and place at new workbook).
for now I trying to hide column “B:G” after copy row to new workbook but it didn’t hide as I wish.

For Each value In uniqueValues ' Loop through each unique value and create a new workbook

Dim NewWs As Worksheet

Set newWorkbook = Workbooks.Add ' Create a new workbook for each unique value

Set NewWs = newWorkbook.Sheets(1)

' Filter and copy rows with the current unique value to new workbook

Set NewWs = newWorkbook.Sheets(1)

ws.Rows(1).Copy Destination:=newWorkbook.Sheets(1).Rows(1) ' Copy header row




For Each cell In rng

If cell.value = value Then

cell.EntireRow.Copy Destination:=newWorkbook.Sheets(1).Range("A" & newWorkbook.Sheets(1).Cells(newWorkbook.Sheets(1).Rows.Count, "A").End(xlUp).row + 1)

End If

Next cell


newWorkbook.Sheets(1).Columns("B:G").Hidden = True

fileName = value

newWorkbook.Sheets(1).Columns.AutoFit ' Autofit all columns in the worksheet

savePath = "C:\Users\" ' Adjust the path where you want to save the files

newWorkbook.SaveAs savePath & fileName ' Save the new workbook with the appropriate name and path

newWorkbook.Close SaveChanges:=False

Set newWorkbook = Nothing


Next value

 

[Joe4]

Welcome to the Board!

That is because you are autofitting the columns AFTER you have hidden them, which will then unhide them!
You need to change the order, i.e. instead of this:

newWorkbook.Sheets(1).Columns("B:G").Hidden = True

fileName = value

newWorkbook.Sheets(1).Columns.AutoFit ' Autofit all columns in the worksheet

change the order to this:

newWorkbook.Sheets(1).Columns.AutoFit ' Autofit all columns in the worksheet
newWorkbook.Sheets(1).Columns("B:G").Hidden = True

fileName = value

 

[shironokuro]

Welcome to the Board!

That is because you are autofitting the columns AFTER you have hidden them, which will then unhide them!
You need to change the order, i.e. instead of this:

newWorkbook.Sheets(1).Columns("B:G").Hidden = True

fileName = value

newWorkbook.Sheets(1).Columns.AutoFit ' Autofit all columns in the worksheet

change the order to this:

newWorkbook.Sheets(1).Columns.AutoFit ' Autofit all columns in the worksheet
newWorkbook.Sheets(1).Columns("B:G").Hidden = True

fileName = value

I really won’t see this, i try fix all in above but it didn’t fix anything. You truly help me.
Thank you very much!!

 

[Joe4]

I am little unclear by your reply. Are you seeing that it DID work, or it did NOT work for you?

Also, I would HIGHLY recommend NOT using reserved words (words already used by Excel and VBA for the names of functions, properties, methods, objects, etc) like "Value" as the name of your variables. Doing so can cause errors and unexpected results.

I would recommend change your "Value" variable to something else, like "val".