Hi Gents

In my Excel work book it has Sheet1 and Sheet2. Following code is applied in Sheet2.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("B")) Is Nothing Then Exit Sub
Set Target = Target.Cells(1)
Application.EnableEvents = False
If Target = "" Then
    Target.Offset(, 5) = ""
End If
Application.EnableEvents = True
End Sub


Info: All tables inserted below are in Sheet 2.
In Sheet 2, B2:D2 entries are populated from Sheet1.
E2:F2 are with formula and result will change based on B2:D2 entries.
And in G2: I2 I have drop down entries using data validation(which will be inserted manually). See table below in Sheet 2:

Col A	Col B	Col C	Col D	Col E	Col F	Col G	Col H	Col I
Row 1	Sl No	Name	RMK	RMK	RMK	RMK	RMK	RMK	RMK
Row 2	1	A	Date	Type	Type	Type	D-Val	D-Val	D-Val
Row 3	2	B	Date	Type	Type	Type	D-Val	D-Val	D-Val
Row 4	3	C	Date	Type	Type	Type	D-Val	D-Val	D-Val

Now as per the code in Sheet 1 : If I revert the entry from Alphabet to Number , In Sheet 2 , it will remove the entries from B2: D2 and subsequently E2: F2 also changes to blank cell. Works fine.
Please note: Column A is not affected by the code in Sheet 1. Keep the Sl No constant.
When the above action happens I want G2:I2 entries to go blank as well, but still with ready for re-entry using D-val.
The above inserted code in Sheet 2, works perfectly when it happens with the last Row entry in Sheet 2- Row 4. Means bottom rows are still there with similar functions ,yet to be filled but when it requires. When cells B4: F4 becomes blank the code keeps G4:I4 as blank , still with dropdown feature available. See below

	Col A	Col B	Col C	Col D	Col E	Col F	Col G	Col H	Col I
Row 1	Sl No	Name	RMK	RMK	RMK	RMK	RMK	RMK	RMK
Row 2	1	A	Date	Type	Type	Type	D-Val	D-Val	D-Val
Row 3	2	B	Date	Type	Type	Type	D-Val	D-Val	D-Val
Row 4	3	--	----	----	----	----	----	-----	-----

But with similar change in Sheet1 , but this time say it belongs to Row 2 in Sheet2, then result shows as below:
	Col A	Col B	Col C	Col D	Col E	Col F	Col G	Col H	Col I
Row 1	Sl No	Name	RMK	RMK	RMK	RMK	RMK	RMK	RMK
Row 2	1	B	Date	Type	Type	Type	---	---	---
Row 3	2	C	Date	Type	Type	Type	---	---	---
Row 4	3	-	----	  ----	 ----	  ----	D-Val	D-Val	D-Val

As per the code in Sheet 1: It removes the entries from B2:D2.
When there is no values in B2:D2 the formula keeps E2:F2 as blank.
Then as per the above code G2:I2 go blank as well.
But as it shown in above table , those cells are being shifted up as well (As per code in Sheet1-This is a right action to avoid the empty cells above).

Here I want the Code inserted: To shift up the cells in Columns G-H and I , with already inserted entries and to be aligned with shifted cells at left side.
With current Code it is not happening, It changes from G2:I3 as blank cells and G3:I3 unchanged.

When such changes happen , the right pattern view which I am looking for is shown below:
	Col A	Col B	Col C	Col D	Col E	Col F	Col G	Col H	Col I
Row 1	Sl No	Name	RMK	RMK	RMK	RMK	RMK	RMK	RMK
Row 2	1	B	Date	Type	Type	Type	D-Val	D-Val	D-Val
Row 3	2	C	Date	Type	Type	Type	D-Val	D-Val	D-Val
Row 4	3	-	----	 ----	 ----	  ----	-----               -----            -----

Any guidance will be highly grateful.