How to Code

excel freeze panes example
Freeze Top Row
In a large worksheet with headings, you might want to make sure that the top row repeats when scrolling, so you can always see it.
In the Ribbon, select View > Freeze Panes.
excel freeze panes ribbon
Select Freeze Top Row.
As you scroll down in your worksheet, the top row remains visible regardless of how far you scroll.
excel freeze panes row68
To remove the freeze, select Unfreeze Panes from the Freeze Panes menu.
excel freeze panes unfreeze
Freeze Panes

You can also freeze more than one row at a time.

1. Position your cursor in the row below the rows you wish to freeze.
   
2. In the Ribbon, select View > Freeze Panes > Freeze Panes.
   excel freeze rows multiple rows

The worksheet freezes Rows 1 to 3. If you scroll down, these rows remain in view.
excel freeze panes multiple rows example
To remove the pane freeze, select Unfreeze Panes from the Freeze Panes menu.
Try our AI Formula Generator
Ex. Sum all values in Column B where Column A equal Q1
Generate

Freeze Top Row in Google Sheets
The process is similar in Google Sheets
In the Menu, click View > Freeze > 1 row.
excel freeze panes google sheets menu
As you scroll down, the top row remains in place.

Quick Methods to Name Ranges
Method 1: Using the Name Box (Fastest)

1. Select the cells or range you want to name.
   
2. Click into the Name Box (the empty box just to the left of the formula bar).
   
3. Type a name (remember: no spaces or special characters are allowed; use underscores instead of spaces, like Total_Sales).
   
4. Press Enter to save the name.

Sub ClearRowBasedOnValue()
Dim tbl As ListObject
Dim i As Long

' Set the table object (Replace "Table1" with your actual table name)
Set tbl = ActiveSheet.ListObjects("Table1")

' Loop through the rows from bottom to top
For i = tbl.ListRows.Count To 1 Step -1
' Check if the cell in a specific column (e.g., "Status") matches your criteria
If tbl.ListRows(i).Range(1, tbl.ListColumns("Status").Index).Value = "Clear" Then

' Clear specific cells in that row by column name
tbl.ListRows(i).Range(1, tbl.ListColumns("Data1").Index).ClearContents
tbl.ListRows(i).Range(1, tbl.ListColumns("Data2").Index).ClearContents

' Alternatively, clear a range of columns in that row
' tbl.ListRows(i).Range.ClearContents
End If
Next i
End Sub

---

Sub ClearRowConstantsByFilter()
Dim tbl As ListObject
Dim row As Range
Dim filterCriteria As String

' Set your table name
Set tbl = ActiveSheet.ListObjects("Table1")

' Define what value you are looking for to trigger the clear
filterCriteria = "DeleteMe"

' Loop through each row in the table's data area
For Each row In tbl.ListObject.DataBodyRange.Rows
' "Filter" check: Only act if the first column matches our criteria
If row.Cells(1, 1).Value = filterCriteria Then
On Error Resume Next ' Avoid error if no constants exist in the row
' Clear everything in the row EXCEPT formulas
row.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
End If
Next row
End Sub

Sub InputBoxWithClipboard()
Dim MyData As Object
Dim ClipText As String
Dim UserInput As String

' 1. Create DataObject to access the clipboard
Set MyData = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

' 2. Retrieve text from clipboard
On Error Resume Next
MyData.GetFromClipboard

' Check if format 1 (Text) exists
If myDatat.GetFormat(1) Then
MsgBox "The clipboard contains text: " & myData.GetText
Else
MsgBox "The clipboard does NOT contain text (it might be empty or an image)."
End If

ClipText = MyData.GetText
On Error GoTo 0

' 3. Display Input Box, preloaded with the clipboard text (as the 3rd argument)
UserInput = InputBox(Prompt:="Edit the text below:", _
Title:="Preloaded Input Box", _
Default:=ClipText)

' 4. Check if user clicked Cancel or left it blank
If UserInput <> "" Then
MsgBox "You entered/submitted: " & UserInput
' Range("A1").Value = UserInput ' Optional: output to a specific cell
End If
End Sub

---

Sub VerifyClipboardText()
Dim myDataObject As New MSForms.DataObject

' Load the clipboard data into the object
myDataObject.GetFromClipboard

' Check if format 1 (Text) exists
If myDataObject.GetFormat(1) Then
MsgBox "The clipboard contains text: " & myDataObject.GetText
Else
MsgBox "The clipboard does NOT contain text (it might be empty or an image)."
End If
End Sub

---

Sub ListRangesInActiveSheet()
Dim nm As Name
Dim targetSheet As Worksheet

' Set the sheet you want to look at
Set targetSheet = ActiveSheet

Debug.Print "--- Named Ranges in " & targetSheet.Name & " ---"

' Loop through all names in the workbook
For Each nm In ActiveWorkbook.Names
On Error Resume Next
' Check if the named range points to the target sheet
If nm.RefersToRange.Worksheet.Name = targetSheet.Name Then
Debug.Print "Name: " & nm.Name & " | Address: " & nm.RefersToRange.Address
End If
On Error GoTo 0
Next nm
End Sub