trying to select two columns

[Mr Rice]

Hi,

Please bare with me as i am just now getting into learning Excel and VBA for my employer.

i am working on a project and i need a vba code to select two columns based off last row of column A and then select blanks within these two columns. Column A will never have blanks since it's set as dynamic within my Master file so finding last row is easy and will never have a blank while using the userform. i want to find all blanks within Column A. I have an inputbox to enter the desired initials i want within the blanks the vba code finds. below is what i used but it selects the full columns. i have been testing several codes but this was the closest i got.

Private Sub FakeName_Click()
 Dim ar
    Dim cell As Range
    Dim inputvalue As String
    Dim msgvalue as VbMsgBoxResult
     inputvalue = InputBox("Please make sure blanks are selected", _
    "Fill Empty Cells")
    
    Range("A:B").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select

msgvalue = msgbox ("Please verify the selected area", vbokcancel + vbinformation, "Ready?")

If vbCancel Then Exit Sub
If vbokay Then
    
    For Each cell In Selection

        If IsEmpty(cell) Then
        cell.Value = inputvalue
            Else: Exit Sub
     End If
    Next
    
End Sub

Test.xlsm
	A	B	C	D	E
1	# of entries	Initials	EXPLANATIONS	Sales Order	Ship Date
2	1	JC		1071380	11/23/2022
3	2	JC	CANCELLED	1071437	11/28/2022
4	3		SHIPPED	1071223	11/29/2022
5	4	JC	SHIPPED	1072028	11/29/2022
6	5	JC	SHIPPED	1072028	11/29/2022
7	6	JC	SHIPPED	1072216	11/29/2022
8	7	JC	SHIPPED	1072226	11/29/2022
9	8	JC	SHIPPED	1072210	11/29/2022
10	9	KW		1067798	11/30/2022
11	10	JC		1071439	11/30/2022
12	11	JC		1071439	11/30/2022
13	12	JC		1071439	11/30/2022
14	13	JC	LABEL ROOM	1071439	11/30/2022
15	14	JC		1071439	11/30/2022
16	15		SHIPPED	1071885	11/30/2022
17	16		SHIPPED	1072231	11/30/2022
18	17	JC	SHIPPED	1072366	11/30/2022
19	18	JC	SHIPPED	1072356	11/30/2022
20	19	JC	LABEL ROOM	1072363	11/30/2022
21	20	Alex	SHIPPED	1072433	11/30/2022
22	21	Alex	SHIPPED	1072426	11/30/2022
23	22	Alex	JUST ENTERED	1072425	11/30/2022
24	23	JC	SHIPPED	1071794	12/1/2022
25	24	JC	SHIPPED	1071794	12/1/2022
26	25	JC	SHIPPED	1071624	12/1/2022
27	26	JC	SHIPPED	1072117	12/1/2022
28	27	JC		1072108	12/1/2022
29	28	JC	LABEL ROOM	1072031	12/1/2022
30	29	JC	LABEL ROOM	1072031	12/1/2022
31	30	JC	SHIPPED	1072032	12/1/2022
32	31	JC	SHIPPED	1072032	12/1/2022
33	32	JC	SHIPPED	1072039	12/1/2022
34	33	JC	SHIPPED	1072039	12/1/2022
35	34	JC	LABEL ROOM	1072049	12/1/2022
36	35	JC	LABEL ROOM	1072049	12/1/2022
37	36	JC	LABEL ROOM	1072049	12/1/2022
38	37	JC	SHIPPED	1072217	12/1/2022
39	38	JC	LABEL ROOM	1072205	12/1/2022
40	39	JC	LABEL ROOM	1072168	12/1/2022
41	40	JC	LABEL ROOM	1072168	12/1/2022
Sheet1

Cell Formulas
Range	Formula
A2:A41	A2	=ROW()-1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D1048576	Expression	=$C2="JUST ENTERED"	text	NO
E2:E1048576	Expression	=$C2="JUST ENTERED"	text	NO
C2:C1048576	Cell Value	contains "JUST ENTERED"	text	NO
C2:C1048576	Expression	=$C2="JUST ENTERED"	text	NO
C2:C1048576	Cell Value	contains "LABEL ROOM"	text	NO
C2:C1048576	Cell Value	contains "Problem"	text	NO
C2:C1048576	Cell Value	contains "NOT PULLED"	text	NO
C2:C1048576	Cell Value	contains "PARTIAL SHIP"	text	NO
C2:C1048576	Cell Value	contains "Small Parts"	text	NO
C2:C1048576	Cell Value	contains "CANCELLED"	text	NO
C2:C1048576	Cell Value	contains "PAINTING"	text	NO
C2:C1048576	Cell Value	contains "SHIPPED"	text	NO
C2:C1048576	Cell Value	contains "PULLED/ NOT DONE"	text	NO
C2:C1048576	Cell Value	contains "BACKORDER"	text	NO

 

[Mr Rice]

i want to find all blanks within Column A. i meant Column B. Sorry for that

 

[Alex Blakenburg]

Welcome to the Forum.

See if this does what you need.

Note: Conditional formatting is a Volatile Function and will slow down your spreadsheet. You have applied it to over 1M rows.
Also by not checking the box "Stop If True" even if the first condition is true it is still going to evaluate all the others so your column C is going to run through all 1M rows 12 times even if the first condition is met.

Private Sub FakeName_Click()
 Dim ar
    Dim cell As Range
    Dim inputvalue As String
    Dim msgvalue As VbMsgBoxResult
    Dim lRow As Long
    Dim rngBlnk As Range
   
     inputvalue = InputBox("Please make sure blanks are selected", _
    "Fill Empty Cells")
   
    lRow = Range("A" & Rows.Count).End(xlUp).Row
    Set rngBlnk = Range("B2:B" & lRow).SpecialCells(xlCellTypeBlanks)
    rngBlnk.Select

msgvalue = MsgBox("Please verify the selected area", vbOKCancel + vbInformation, "Ready?")

If msgvalue = vbCancel Then Exit Sub
If msgvalue = vbOK Then
   
    For Each cell In rngBlnk
        If IsEmpty(cell) Then
            cell.Value = inputvalue
        Else: Exit Sub
        End If
    Next
End If
   
End Sub

 

[Mr Rice]

Looks like your code did the trick! Thank you very much.

 

[Alex Blakenburg]

You're welcome. Glad we could help.