Clear Content based on another cell value (Empty)

[gzell]

Clear Content on Column C, D, E, based on cell G being empty and loop through worksheet
In sheet below, names would be cleared (column C, D, E, if Column G in same row is empty

Second item, is after column C, D, E are clear in row where empty cell is found,
then remove duplicate names in column C, D, E. If this should be on another thread, please let me know.
After this step, names in column C, D, E, would be cleared except for the names in row 917 and 950

Row_ID	Person_ID	Last Name	First Name	Middle	Data_ID	Research Data
916	36	Barber	Silas	H
917	36	Barber	Silas	H	1_1	Army	Confederacy
918	36	Barber	Silas	H	1_2	Location	Texas
919	36	Barber	Silas	H	1_3	Regiment	10th Regiment Texas Infantry (Nelson's)
920	36	Barber	Silas	H	1_4	Function	Infantry
921	36	Barber	Silas	H	1_5	Company	B
922	36	Barber	Silas	H	1_6	Rank	Private
923	36	Barber	Silas	H	1_7	Age	38
924	36	Barber	Silas	H	1_8	Residence	--
925	36	Barber	Silas	H	1_9	Enrolled	Springfield, Texas
926	36	Barber	Silas	H	1_10	Date	October 10, 1861
927	36	Barber	Silas	H	1_11	Enlisted	Virginia Point, Texas
928	36	Barber	Silas	H	1_12	Date	October 23, 1861
929	36	Barber	Silas	H	3_1	Detail	Commissary for Company
930	36	Barber	Silas	H	3_1	Detail	February, 1862
931	36	Barber	Silas	H	3_1	Detail	Mechanic
932	36	Barber	Silas	H	3_1	Date	August 28, 1862
933	36	Barber	Silas	H	8_1	Returned to Duty	September 1, 1862
934	36	Barber	Silas	H	2_1	Captured	Arkansas Post, Arkansas
935	36	Barber	Silas	H	2_1	Date	January 11, 1863
936	36	Barber	Silas	H	2_1	Forwarded	St. Louis, Missouri via Boats
937	36	Barber	Silas	H	2_1	Forwarded	Camp Douglas, Illinois via Rail
938	36	Barber	Silas	H	2_1	Prisoner	Camp Douglas, Illinois
939	36	Barber	Silas	H	2_1	Paroled	April 1, 1863
940	36	Barber	Silas	H	2_1	Forwarded for Forward for Exchange	City Point, Virginia
941	36	Barber	Silas	H	2_1	Date	April 7, 1863
942	36	Barber	Silas	H	2_10	Hospital	General Hospital
943	36	Barber	Silas	H	2_10	Location	Petersburg, Virginia
944	36	Barber	Silas	H	2_10	Cause	Dibilitas
945	36	Barber	Silas	H	2_10	Admited	May 7, 1863
946	36	Barber	Silas	H	8_1	Returned to Duty	June 16, 1863
947	36	Barber	Silas	H	3_1	Detail	Regimental Clerk
948	36	Barber	Silas	H	3_1	Date	April 7, 1864
949	37	Barker	Enoch	W
950	37	Barker	Enoch	W	1_1	Army	Confederacy
951	37	Barker	Enoch	W	1_2	Location	Texas
952	37	Barker	Enoch	W	1_3	Regiment	10th Regiment Texas Infantry (Nelson's)
953	37	Barker	Enoch	W	1_4	Function	Infantry
954	37	Barker	Enoch	W	1_5	Company	E
955	37	Barker	Enoch	W	1_6	Rank	Private
956	37	Barker	Enoch	W	1_7	Age	18
957	37	Barker	Enoch	W	1_8	Residence	--
958	37	Barker	Enoch	W	1_9	Enrolled	Parker County, Texas
959	37	Barker	Enoch	W	1_10	Date	October 12, 1861
960	37	Barker	Enoch	W	1_11	Enlisted	Houston, Texas
961	37	Barker	Enoch	W	1_12	Date	October 23, 1861
962	37	Barker	Enoch	W	13_1	Discharged	Camp Yell, Arkansas
963	37	Barker	Enoch	W	13_1	Date	May 25, 1862

 

[Fluff]

This will clear C:E where G is empty

Sub gzell()
    With Range("G2:G" & Range("C" & Rows.Count).End(xlUp).Row)
        Intersect(.SpecialCells(xlBlanks).EntireRow, Range("C:E")).ClearContents
    End With
End Sub

After the above code is run, will there always be a blank row between different names?

 

[gzell]

Thank you so much. Worked pefect

 

[Fluff]

You're welcome & thanks for the feedback.

If you answer my question in post#2 I can help you with the rest.

 

[gzell]

Yes there will be blank row between. This is a project I am researching for a Civil War Regiment that my Great Great Grandfather served.

 

[Fluff]

Ok, how about

Sub gzell()
    With Range("G2:G" & Range("C" & Rows.Count).End(xlUp).Row)
        Intersect(.SpecialCells(xlBlanks).EntireRow, Range("C:E")).ClearContents
    End With
    With Range("E2", Range("C" & Rows.Count).End(xlUp))
      .SpecialCells(xlConstants).Offset(1).ClearContents
   End With
End Sub

 

[gzell]

Thanks for the help. Worked great and now I can get back to cleaning up my entries and verifying data entries.

 

[Fluff]

You're welcome & thanks for the feedback