Forum Discussion

Zsmbek1963's avatar
Zsmbek1963
Copper Contributor
Feb 27, 2023

How to edit cells

Hello!   I'd like to know how to make one-cell spaces beween the text-cells generally. I know how to do it one-by-one but it takes a lot of time. I'd like it boost.   Can you help me?   Your si...
Excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Feb 27, 2023

    Zsmbek1963 

    Save the workbook before you do the following.

    If you don't like the result, you can close the workbook without saving it.

    Press Alt+F11 to activate the Visual Basic Editor.

    Select Insert > Module (or press Alt+I, M) to create a new code module.

    Copy the code listed below into the code window.

    With the insertion point anywhere in the code, press F5 to run it.

    Switch back to Excel to inspect the result.

    If you won't need the macro anymore, save and close the workbook. Excel will display a warning that macros will be lost. Click Yes to save the workbook anyway.

    If you'd like to keep the macro, save the workbook as a macro-enabled workbook (*.xlsm).

    You will have to allow macros when you open it.

     

    The code:

    Sub InsertRows()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For r = m To 2 Step -1
        Range("A" & r).EntireRow.Insert
    Next r
    Application.ScreenUpdating = True
End Sub
HansVogelaar's avatar
HansVogelaar
MVP

Zsmbek1963 

It would probably be possible with a macro, but we'd need to know more about the layout of your data.

 

Wouldn't it be easier to increase the column widths and/or the row heights?

Zsmbek1963's avatar
Zsmbek1963
Copper Contributor
Feb 27, 2023
Hi!
Unfortunately, increasing column widths or row is not an option. I am making a subtitle-translation for a theatre performance and for the subtitle-arranging program I have to leave one-cell spaces between each text-cell.
Being an absolute beginner in Excel, would you so kind to tell me, what is macro?

Thank you in advance:

Zsmbek1963
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 27, 2023

    Zsmbek1963 

    Do you want to insert an empty column in between the filled columns?

    Do you want to insert an empty row in between the filled rows?

    Or both?

    • Zsmbek1963's avatar
      Zsmbek1963
      Copper Contributor
      Feb 27, 2023
      The latter. I would like to insert empty row in between filled rows.
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Feb 27, 2023

        Zsmbek1963 

        Save the workbook before you do the following.

        If you don't like the result, you can close the workbook without saving it.

        Press Alt+F11 to activate the Visual Basic Editor.

        Select Insert > Module (or press Alt+I, M) to create a new code module.

        Copy the code listed below into the code window.

        With the insertion point anywhere in the code, press F5 to run it.

        Switch back to Excel to inspect the result.

        If you won't need the macro anymore, save and close the workbook. Excel will display a warning that macros will be lost. Click Yes to save the workbook anyway.

        If you'd like to keep the macro, save the workbook as a macro-enabled workbook (*.xlsm).

        You will have to allow macros when you open it.

         

        The code:

        Sub InsertRows()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For r = m To 2 Step -1
        Range("A" & r).EntireRow.Insert
    Next r
    Application.ScreenUpdating = True
End Sub

Resources