In My excel, heading is mentioned in “A2” cell so we should not look for header in “A1” cell. Already filter is applied from A2 Cell
we have to filer and remove “Blank” and “#N/A” Values. After removing these two things we have to copy the whole Column from A2 Cell to till where data is available and paste using paste button on left side of excel and choose the Option “Values(V)” in Paste values option in the same column from A2 Cell. Suggest a best way to get it. Even its a VB Code also it would be good.
1 Like
1 Like
1 Like
Input:
VBA code:
Sub DeleteZeroAndBlankCellsInColumnA()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
' Define the worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your actual sheet name
' Find the last row in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Loop through each cell in column A
For i = lastRow To 1 Step -1
If ws.Cells(i, 1).Value = 0 Or ws.Cells(i, 1).Value = "" Then
' Delete the row if the cell value is 0 or blank
ws.Rows(i).Delete
End If
Next i
End Sub
Refer the below workflow for more Understanding. Sheet1 is the output after removing #N/A and blank cell values.
Output:
BlankProcess18.zip (47.5 KB)
Regards
I want to keep the filter don’t remove the filter and I want to retain A1 cell just because that #N/A contains formula and paste the value from A3 Cell
A2 cell is your heading. Animals is your Column name already there is filter is their put filter and remove #N/A and Blanks and copy and paste from A3 cell because values are present in that only.
Input:
Use the below macros code:
Sub FilterColumnA()
Dim ws As Worksheet
Dim rng As Range
' Define the worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your actual sheet name
' Define the range to apply the filter (assuming data starts from A2)
Set rng = ws.Range("A2", ws.Cells(ws.Rows.Count, "A").End(xlUp))
' Apply the filter
rng.AutoFilter Field:=1, Criteria1:="<>#N/A", Operator:=xlAnd, Criteria2:="<>"
End Sub
Output:
Filter.txt (454 Bytes)
Regards
If the filter is already their in the excel means do we need to make any change in code?
If you have an existing Filter in Animal cell no problem please use the below code and it will work.
Sub FilterColumnA()
Dim ws As Worksheet
Dim rng As Range
' Define the worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your actual sheet name
' Define the range to apply the filter (assuming data starts from A2)
Set rng = ws.Range("A2", ws.Cells(ws.Rows.Count, "A").End(xlUp))
' Apply the filter
rng.AutoFilter Field:=1, Criteria1:="<>#N/A", Operator:=xlAnd, Criteria2:="<> "
End Sub
Regards
This code will copy the column A and paste it from A3 column using paste as values option?
The above code will filter from A2 cells with #N/A and blanks and hide those rows and return the remaining data.
Do you want to paste that data in any other column.please specify.
Hope you understand the above code
Regards
Thank vrdabberu it’s working, it’s actually filtered but filter option nut showing #N/A is unchecked alll the values are unchecked.
1 Like
You’re welcome @Gopi_Krishna1
Happy Automation!!
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
Yoichi
Raja.G
mkt.scott4