Dynamic Range selection

[earthworm]

I am using the below macro to apply X on empty cell in column Heading 7 . However when i play the macro this does not work and its not dynamic despite of using relative mode during macro recording . The Macro length needs to be dynamic . Please assist.

S.no	Heading 4	Heading 5	Heading 6	Heading 7
1				X
2				X
3				X
4				d
5				X
6				X
7				X
8		G		10​

Sub Macro4()
'
' Macro4 Macro
'

'
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(-8, 4).Range("A1:A9").Select
    ActiveCell.Offset(0, 4).Range("A1").Activate
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "X"
    Range("A1").Select
End Sub

 

[Fluff]

How about

Sub earthworm()
   Range("E1:E" & Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).Value = "X"
End Sub

 

[earthworm]

I tried the above but its not dynamic . Example the macro will not function if the value is non numeric in S.no . I tried COUNTA in macro but its not working.
Secondly the starting position of range in in column E must be as per last value in s.no. If there is any value missing in last cell of column E then this macro is not working correctly. Please suggest and also share step by step . I wasted hours to understand but still no clue.

 

[Fluff]

Sorry but I don't understand what you are saying.
The code I posted will put an X in every empty cell in col E down to the last row of data in col A.

 

[earthworm]

Sorry but I don't understand what you are saying.
The code I posted will put an X in every empty cell in col E down to the last row of data in col A.

Please extend the rows and input any value in s.noand empty the last value in column E and try running the macro . You will get the picture.

 

[Fluff]

I get an X in every empty cell in col E until the last row with a value in col A.
Are the blank cells in col E actually empty?

 

[earthworm]

Please try the macro you shared on below

S.no	Heading 4	Heading 5	Heading 6	Heading 7
1				X
2				X
3				X
4				d
5				X
6				X
7				X
8		G		10
G
H
Y

 

[Fluff]

How about

Sub earthworm()
   Range("E1:E" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks).Value = "X"
End Sub

 

[earthworm]

PER

How about

Sub earthworm()
   Range("E1:E" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks).Value = "X"
End Sub

Perfect . I also searched from youtube and found this which is also good in selection . please advice is this good too ?

 Sub dynamic_range()
    dy_range = ActiveSheet.UsedRange.Rows.Count
    Range("E1:E" & dy_range).Select
    End Sub

 

[Fluff]

There is very rarely any need to use select.