replace cell contents in range

[KyleG]

I need to have a macro that looks down the A column for the last cell with data (Upto A1800 have formula that displays nothing unless condition meet on another page). This will form the last row of the area that the replace formula acts in. Lets call it LR
Like wise it needs to look across the 1 column for the last column with data This forms the last column for the replace formula. Lets call it LC.
Then all cells in the range B3:to LC-LR that are blank get an x.

Can anyone help please. Thanks in advance

 

[stanleydgromjr]

KyleG,

If I understand you correctly.


Before the macro:

Excel Workbook
	A	B	C	D	E	F	G	H
3	3		3				3
4	4		4				4
5	5		5				5
6	6		6				6
7	7		7				7
8	8		8				8
9	9		9				9
10	10		10				10
11	11		11				11
12	12				12		12
13	13				13		13
14	14				14		14
15	15				15		15
16	16				16		16
17	17				17		17
18	18				18		18
19	19				19		19
20	20				20		20
21	21				21		21
22	22				22		22
23
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H
3	3	x	3	x	x	x	3
4	4	x	4	x	x	x	4
5	5	x	5	x	x	x	5
6	6	x	6	x	x	x	6
7	7	x	7	x	x	x	7
8	8	x	8	x	x	x	8
9	9	x	9	x	x	x	9
10	10	x	10	x	x	x	10
11	11	x	11	x	x	x	11
12	12	x	x	x	12	x	12
13	13	x	x	x	13	x	13
14	14	x	x	x	14	x	14
15	15	x	x	x	15	x	15
16	16	x	x	x	16	x	16
17	17	x	x	x	17	x	17
18	18	x	x	x	18	x	18
19	19	x	x	x	19	x	19
20	20	x	x	x	20	x	20
21	21	x	x	x	21	x	21
22	22	x	x	x	22	x	22
23
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module (on the right pane).

Option Explicit
Sub Put_x_inBlankCells()
Dim LR As Long, LC As Long
Application.ScreenUpdating = False
With ActiveSheet
  LR = .Range("A3").End(xlDown).Row
  LC = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
  .Range(Cells(3, 2), Cells(LR, LC)).SpecialCells(xlCellTypeBlanks) = "x"
End With
Application.ScreenUpdating = True
End Sub

Then run the "Put_x_inBlankCells" macro.


Have a great day,
Stan