vba formula does not work

Question

vba formula does not work - i am working on product data base file where i had made final sheet where data bring through vlookup formula

Sub HideBlankRowsAndColumns() Dim ws As WorksheetDim lastRow As LongDim lastCol As LongDim rowEmpty As BooleanDim colEmpty As BooleanDim i As LongDim j As Long' Set the worksheet variable to the "Product" sheetSet ws = ThisWorkbook.Sheets("Product")' Find the last used row and column in the specified rangelastRow = 50lastCol = 20' Hide blank rowsFor i = 1 To lastRowrowEmpty = TrueFor j = 1 To lastColIf ws.Cells(i, j).Value = vbNullString ThenrowEmpty = FalseExit ForEnd IfNext jIf rowEmpty Then ws.Rows(i).Hidden = TrueNext i' Hide blank columnsFor j = 1 To lastColcolEmpty = TrueFor i = 1 To lastRowIf ws.Cells(i, j).Value = vbNullString ThencolEmpty = FalseExit ForEnd IfNext iIf colEmpty Then ws.Columns(j).Hidden = TrueNext jEnd Sub

Answer 1

Hi,

a/before

.

b/after

.

vba

Sub Hide_Empty_RowsColumns()

'## 30/04/2024 ##

Dim ws As Worksheet

Set ws = ActiveSheet

Dim LastRow As Integer, LastCol As Integer, x As Integer

Application.ScreenUpdating = False

ws.Columns(1).EntireRow.Hidden = False

ws.Rows(1).EntireColumn.Hidden = False

LastRow = ws.Cells.Find(What:="*", _

SearchOrder:=xlByRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row

LastCol = ws.Cells.Find(What:="*", _

SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column

'

For i = LastRow To 1 Step -1

x = WorksheetFunction.CountBlank(ws.Cells(i, 1).Resize(, LastCol))

If x = LastCol Then

ws.Rows(i).Hidden = True

End If

Next i

For i = LastCol To 1 Step -1

x = WorksheetFunction.CountBlank(ws.Cells(1, i).Resize(LastRow))

If x = LastRow Then

ws.Columns(i).Hidden = True

End If

Next i

Application.ScreenUpdating = True

End Sub

Answer 2

Could you share a test file and show what's wrong on your code result?

For sharing a sample workbook, upload to OneDrive or any other cloud drives. Then post a link here.

Why a sample file is important for troubleshooting. How to do it. - Microsoft Community