当前位置：文江博客话题详情

如何在文本输入期间自动调整 Excel 中的列宽

发布于 2024-08-17 22:23:01 字数 170 浏览 11 评论 0原文

我通常会尝试避免在 Excel 中使用 VBA，但是能够在单元格中键入文本并使其列变宽或变窄以容纳输入或删除时剩余的文本会很方便。

当然，这取决于该列中其他单元格中文本的长度。

我猜你可能会这么称呼它：“键入时自动调整”。

有没有一种简单的方法可以在合适的处理程序中做到这一点？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

静待花开 2024-08-24 22:23:02

在现代版本的 Excel（2010+，我不知道 2007 版本）中，您可以在单元格中输入数据后立即使用宏来调整列大小以适应数据。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.ScreenUpdating = False
    ActiveSheet.Columns.AutoFit
    Application.ScreenUpdating = True
End Sub

将宏放入 ThisWorkbook 模块中

In modern versions of Excel (2010+, I don't know about the 2007 version) you could use a macro to resize your column to fit data as soon you finish entering data in a cell.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.ScreenUpdating = False
    ActiveSheet.Columns.AutoFit
    Application.ScreenUpdating = True
End Sub

Put the macro in the ThisWorkbook module

回复收藏 0 原文

澉约 2024-08-24 22:23:02

这将自动适应列宽

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Columns.AutoFit
End Sub

This will automatically fit columns width

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Columns.AutoFit
End Sub

回复收藏 0 原文

初心未许 2024-08-24 22:23:02

这是我的答案，我验证它有效：

Private Sub Worksheet_Change(ByVal Target As Range)
If Target Is Nothing Then
      Exit Sub
    Else
      With Target
        .Columns.Select
        .Columns.AutoFit
      End With
End If
End Sub

Here is my answer, I verified it works:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target Is Nothing Then
      Exit Sub
    Else
      With Target
        .Columns.Select
        .Columns.AutoFit
      End With
End If
End Sub

回复收藏 0 原文

金橙橙 2024-08-24 22:23:01

我不确定是否有办法在您打字时执行此操作。我认为 Excel 通常会在触发 worksheet_change 事件之前拉伸单元格视图以显示所有文本。

在您更改目标并将目标移动到新范围后，此代码将调整列的大小。将其放入工作表模块中。

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim nextTarget As Range
    
    Set nextTarget = Range(Selection.Address) 'store the next range the user selects
    
    Target.Columns.Select 'autofit requires columns to be selected
    Target.Columns.AutoFit
    
    nextTarget.Select
End Sub

如果您想针对特定列执行此操作，则需要像这样检查目标列：

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim nextTarget As Range
    
    Set nextTarget = Range(Selection.Address) 'store the next range the user selects
    
    If Target.Column = 1 Then
    
        Target.Columns.Select 'autofit requires columns to be selected
        Target.Columns.AutoFit
    
        nextTarget.Select
    End If
End Sub

I'm not sure if there is a way to do it while you're typing. I think Excel generally stretches the cell view to display all the text before it fires the worksheet_change event.

This code will resize the column after you have changed and moved the target to a new range. Place it in the worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim nextTarget As Range
    
    Set nextTarget = Range(Selection.Address) 'store the next range the user selects
    
    Target.Columns.Select 'autofit requires columns to be selected
    Target.Columns.AutoFit
    
    nextTarget.Select
End Sub

If you're looking to do it for a particular column, you would need to check the target column like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim nextTarget As Range
    
    Set nextTarget = Range(Selection.Address) 'store the next range the user selects
    
    If Target.Column = 1 Then
    
        Target.Columns.Select 'autofit requires columns to be selected
        Target.Columns.AutoFit
    
        nextTarget.Select
    End If
End Sub

回复收藏 0 原文

~没有更多了~