need code help or correction

[VBABEGINER]

Can any one pls help here..
Filter Col AB as a blank values. Select first row after header and give cell name as "False". Then copy this "False" value for rest of the filtered rows.

LastRow = Range("A2", Range("A" & Rows.Count).End(xlUp)).Count

Range("AB1").Select
ActiveSheet.Range("$A$1:$AM$" & LastRow).AutoFilter Field:=28, Criteria1:="="

Dim r As Long
r = Range("A2:A" & Rows.Count).SpecialCells(xlVisible)(1).Row

Range("AB" & r).Select
ActiveCell.FormulaR1C1 = "FALSE"

'To get last row number after filter the COl AB
Dim FinalRowFiltered As Long, dataRange As Range
Set dataRange = Range("$AB$2:$AB$" & LastRow)
With dataRange.SpecialCells(xlCellTypeVisible)
    FinalRowFiltered = .Areas(.Areas.Count).Row + .Areas(.Areas.Count).Rows.Count
    'MsgBox FinalRowFiltered
End With

Range("AB" & r).Select
Selection.Copy
'instead of below line..
Range("AB3:AB" & FinalRowFiltered).Select
'I need in this way
Range("AB"&r+1 ":" "AB" & FinalRowFiltered).Select

Selection.SpecialCells(xlCellTypeVisible).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("AB1").Select
ActiveSheet.Range("$A$1:$AM$" & LastRow).AutoFilter Field:=28, Criteria1:="False"

 

[davesexcel]

It looks like you are filtering for blanks and then making the blank cells ="False"

Sub UsingFilter()
    Dim sh As Worksheet, fRng As Range, LstRw As Long
    Set sh = ActiveSheet
    With sh
        LstRw = .Cells(.Rows.Count, "AB").End(xlUp).Row
        .Range("A1").AutoFilter Field:=28, Criteria1:="="
        On Error Resume Next
        Set fRng = .Range("AB2:AB" & LstRw).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not fRng Is Nothing Then
            fRng.Value = "False"
        End If
        If .AutoFilterMode = True Then .AutoFilterMode = False
    End With
End Sub

You can also make this without filtering for the blank cells
You need the error trap in case there are no blanks.

Sub changeSpaces()

On Error Resume Next
Range("AB1:AB" & Cells(Rows.Count, "AB").End(xlUp).Row).SpecialCells(xlCellTypeBlanks) = "False"
On Error GoTo 0

End Sub

 

[VBABEGINER]

It looks like you are filtering for blanks and then making the blank cells ="False"

Sub UsingFilter()
    Dim sh As Worksheet, fRng As Range, LstRw As Long
    Set sh = ActiveSheet
    With sh
        LstRw = .Cells(.Rows.Count, "AB").End(xlUp).Row
        .Range("A1").AutoFilter Field:=28, Criteria1:="="
        On Error Resume Next
        Set fRng = .Range("AB2:AB" & LstRw).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not fRng Is Nothing Then
            fRng.Value = "False"
        End If
        If .AutoFilterMode = True Then .AutoFilterMode = False
    End With
End Sub

You can also make this without filtering for the blank cells
You need the error trap in case there are no blanks.

Sub changeSpaces()

On Error Resume Next
Range("AB1:AB" & Cells(Rows.Count, "AB").End(xlUp).Row).SpecialCells(xlCellTypeBlanks) = "False"
On Error GoTo 0

End Sub

Hi Dave.. Thank You for your reply. both worked. Specially I liked the 2nd one.. This is what we get to know from this board.. For me I never understand from how to get this some nice-nice vba lines..

I would be lucky if I get someone who will teach me end to end how to write vba codes.. Hopefully..

 

[VBABEGINER]

Hi

It looks like you are filtering for blanks and then making the blank cells ="False"

Sub UsingFilter()
    Dim sh As Worksheet, fRng As Range, LstRw As Long
    Set sh = ActiveSheet
    With sh
        LstRw = .Cells(.Rows.Count, "AB").End(xlUp).Row
        .Range("A1").AutoFilter Field:=28, Criteria1:="="
        On Error Resume Next
        Set fRng = .Range("AB2:AB" & LstRw).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not fRng Is Nothing Then
            fRng.Value = "False"
        End If
        If .AutoFilterMode = True Then .AutoFilterMode = False
    End With
End Sub

You can also make this without filtering for the blank cells
You need the error trap in case there are no blanks.

Sub changeSpaces()

On Error Resume Next
Range("AB1:AB" & Cells(Rows.Count, "AB").End(xlUp).Row).SpecialCells(xlCellTypeBlanks) = "False"
On Error GoTo 0

End Sub

Dave, Sorry to interrupt you again.. I want to use 2nd method and with that the last row of Col AB left as a blank only. means that not filled with "False" value.. could you pls suggest..

Just cross check with 1st code too, even then last record not get filled with "False". Is that cell type something different? If yes, how to identify the same..? Pls guide..
just

 

[VBABEGINER]

*Adding one more comment..
Just I observe that, on the blank areas of the COl AB, wherever we've filled values, it goes as "FALSE" (means in capital order). There are other "False" values also present, and hence when I look at normal view (w.out filter) this looks 2 values - FALSE and False..

In filter it's showing "FALSE" (contains - FALSE and False).. Hope you got it what I'm trying to convince..

 

[Alex Blakenburg]

Try changing this:

Range("AB1:AB" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeBlanks) = False

 

[VBABEGINER]

Try changing this:

Range("AB1:AB" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeBlanks) = False

Hi Alex, Thank You for your reply. Ok, so we can do such basic change also.. that's really good, thanks for teaching..!!
But again, my one question remain same even by applying this line of code, that it's still showing - FALSE and False in column.

I'm not sure directly we can do this using one vba line code, but just a small thought to get all False values in correct order, can we use "Proper" formula..? then all the false values may looks in "False" instead of looking - FALSE and False

Could you please suggest how to add the formula.. Or may be any easy method..

 

[Alex Blakenburg]

How are the other "False" values getting in there ?
Formula ? Manual entry ?

 

[VBABEGINER]

How are the other "False" values getting in there ?
Formula ? Manual entry ?

This is data I'm extracting and Col Ab contains 2 values by default, True and False. The blank values should be replace with False..

 

[VBABEGINER]

Hi All, could anyone suggest here pls..