企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 336|回复: 2

VBA in excel not rebuilding

[复制链接]

71

主题

366

帖子

519

积分

高级会员

Rank: 4

积分
519
QQ
发表于 2014-3-18 20:45:02 | 显示全部楼层 |阅读模式
Hi,

I am a real beginner with VB code.

I have a VB code in excel to hide rows where values are 0.

This works 100% untill I close the sheet and re opens it.

I save the sheet as xlsm (macro enabled).

When I open the sheet my vb code to hide the rows does not work. It is still there but it does not rebuild.

Any help is appreciated.

See attached sheet for my code.

I should add refresh or something somewhere I think.

Thank you
回复

使用道具 举报

76

主题

403

帖子

586

积分

高级会员

Rank: 4

积分
586
QQ
发表于 2014-3-18 22:31:45 | 显示全部楼层
your code works fine for me in Excel 2007 with automatic calculation turned on, e.g. for retrieving values from another workbook on opening.
回复 支持 反对

使用道具 举报

81

主题

410

帖子

598

积分

高级会员

Rank: 4

积分
598
QQ
发表于 2014-3-18 23:21:42 | 显示全部楼层
Somewhat condensed notation:

Code: Private Sub Worksheet_Calculate()

    Dim LstRw As Long, Rw As Long
   
    Application.ScreenUpdating = False

    '''Un-hide all rows to start with
    Rows("15:" & LstRw).Hidden = False

    '''Define LstRw as the last row in column F with data.
    LstRw = Cells(Rows.Count, "G").End(xlUp).Row

    ''' Go through column G (starting at row 15) & hide all rows with a value of 0
    For Rw = 15 To LstRw
        If Cells(Rw, "G") = "" Then Rows(Rw).Hidden = True
    Next

    Application.ScreenUpdating = True
   
End Sub

I also do not see why this would not work.
Although in general I dislike the Worksheet_Calculate() event. I would rather go for a Worksheet_Activate() event.

By the way, instead of looping, consider an autofilter / advanced filter. And have a look at SpecialCells too.

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|小黑屋|企业绩效管理网 ( 京ICP备14007298号   

GMT+8, 2020-9-21 13:48 , Processed in 0.151261 second(s), 12 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表