��权限没有,则隐藏 function forum_list_access_filter($forumlist, $gid, $allow = 'allowread') { global $grouplist; if (empty($forumlist)) return array(); if (1 == $gid) return $forumlist; $forumlist_filter = $forumlist; $group = $grouplist[$gid]; foreach ($forumlist_filter as $fid => $forum) { if (empty($forum['accesson']) && empty($group[$allow]) || !empty($forum['accesson']) && empty($forum['accesslist'][$gid][$allow])) { unset($forumlist_filter[$fid]); } unset($forumlist_filter[$fid]['accesslist']); } return $forumlist_filter; } function forum_filter_moduid($moduids) { $moduids = trim($moduids); if (empty($moduids)) return ''; $arr = explode(',', $moduids); $r = array(); foreach ($arr as $_uid) { $_uid = intval($_uid); $_user = user_read($_uid); if (empty($_user)) continue; if ($_user['gid'] > 4) continue; $r[] = $_uid; } return implode(',', $r); } function forum_safe_info($forum) { //unset($forum['moduids']); return $forum; } function forum_filter($forumlist) { foreach ($forumlist as &$val) { unset($val['brief'], $val['announcement'], $val['seo_title'], $val['seo_keywords'], $val['create_date_fmt'], $val['icon_url'], $val['modlist']); } return $forumlist; } function forum_format_url($forum) { global $conf; if (0 == $forum['category']) { // 列表URL $url = url('list-' . $forum['fid'], '', FALSE); } elseif (1 == $forum['category']) { // 频道 $url = url('category-' . $forum['fid'], '', FALSE); } elseif (2 == $forum['category']) { // 单页 $url = url('read-' . trim($forum['brief']), '', FALSE); } if ($conf['url_rewrite_on'] > 1 && $forum['well_alias']) { if (0 == $forum['category'] || 1 == $forum['category']) { $url = url($forum['well_alias'], '', FALSE); } elseif (2 == $forum['category']) { // 单页 $url = ($forum['threads'] && $forum['brief']) ? url($forum['well_alias'] . '-' . trim($forum['brief']), '', FALSE) : url($forum['well_alias'], '', FALSE); } } return $url; } function well_forum_alias() { $forumlist = forum_list_cache(); if (empty($forumlist)) return ''; $key = 'forum-alias'; static $cache = array(); if (isset($cache[$key])) return $cache[$key]; $cache[$key] = array(); foreach ($forumlist as $val) { if ($val['well_alias']) $cache[$key][$val['fid']] = $val['well_alias']; } return array_flip($cache[$key]); } function well_forum_alias_cache() { global $conf; $key = 'forum-alias-cache'; static $cache = array(); // 用静态变量只能在当前 request 生命周期缓存,跨进程需要再加一层缓存:redis/memcached/xcache/apc if (isset($cache[$key])) return $cache[$key]; if ('mysql' == $conf['cache']['type']) { $arr = well_forum_alias(); } else { $arr = cache_get($key); if (NULL === $arr) { $arr = well_forum_alias(); !empty($arr) AND cache_set($key, $arr); } } $cache[$key] = empty($arr) ? '' : $arr; return $cache[$key]; } ?>excel - How to determine the resulting range of TextToColumns - Stack Overflow
最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

excel - How to determine the resulting range of TextToColumns - Stack Overflow

programmeradmin4浏览0评论

Is there a good way to get the resulting range after using Range.TextToColumns in VBA?

What I'm trying to do is split some data into columns, and then perform an action on each resulting cell. If I knew ahead of time how big the resulting range would be I could just select it, but I want to do it dynamically for any size of data.

Call Selection.TextToColumns(DataType:=xlDelimited, comma:=True)
resultRange = ?????
Call OtherSub(resultRange)

Is there a good way to get the resulting range after using Range.TextToColumns in VBA?

What I'm trying to do is split some data into columns, and then perform an action on each resulting cell. If I knew ahead of time how big the resulting range would be I could just select it, but I want to do it dynamically for any size of data.

Call Selection.TextToColumns(DataType:=xlDelimited, comma:=True)
resultRange = ?????
Call OtherSub(resultRange)
Share Improve this question asked Jan 17 at 20:59 Helos35Helos35 1211 silver badge13 bronze badges 2
  • Selection.Cells(1).CurrentRegion – Spectral Instance Commented Jan 17 at 21:27
  • Iterate through selection and use Ubound(Split(Selection.Cells(n,1),",")) for each cell to get the number of columns, then just track which is your Max. – Frank Ball Commented Jan 24 at 19:03
Add a comment  | 

3 Answers 3

Reset to default 1

The sample script assumes that all rows in your data contain an equal number of columns after the splitting process.

Using CurrentRegion is a straightforward approach (as @Spectral Instance's comment) if there are no blank columns after splitting. Otherwise, additional code is needed to determine the number of columns.

Microsoft documentation:

Split function

Range.CurrentRegion property (Excel)

Sub demo()
    Dim r As Range
    With Range("A1:A3")
        .TextToColumns DataType:=xlDelimited, comma:=True
        Set r = .CurrentRegion
        Debug.Print r.Address  ' => $A$1:$C$3
    End With
    
    With Range("A7:A9")
        Dim sTxt As String: sTxt = .Cells(1)
        .TextToColumns DataType:=xlDelimited, comma:=True
        Set r = .CurrentRegion
        Debug.Print r.Address  ' => $A$7:$B$9 only get the first block cells
        Dim iCol As Long: iCol = UBound(Split(sTxt, ",")) + 1
        Set r = .Resize(, iCol)
        Debug.Print r.Address  ' => $A$7:$E$9
    End With
End Sub

For example (assuming there is no data already present to the right of cells to be split):

Dim f As Range

With ActiveSheet.Range("A8:A10")
    'these next 2 lines are for testing only....
    .EntireRow.Clear
    .Value = .Parent.Range("A2:A4").Value


    .TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
        Space:=True, Other:=True, OtherChar:="|"
    Set f = .EntireRow.Find(what:="*", LookIn:=xlValues, _
              searchorder:=xlByColumns, SearchDirection:=xlPrevious)
    
    Debug.Print "Last column: " & f.Column '>>8 in this case
    
End With

Values before and after splitting:

I ended up just manually interrogating the data to figure out the result. It seems like this puts me most of the way to just re-coding text to columns anyway, but it works.

Set selectedRange = Selection

maxCol = 0
For Each rng In selectedRange
    sTxt = rng.Value
    iCol = UBound(Split(sTxt, ",")) + 1
    If iCol > maxCol Then
        maxCol = iCol
    End If
Next rng

Call Selection.TextToColumns(DataType:=xlDelimited, _
    comma:=True, _
    Space:=False, semicolon:=False, Tab:=False, other:=False, _
    ConsecutiveDelimiter:=False)

Set rng = selectedRange.Resize(, maxCol)
发布评论

评论列表(0)

  1. 暂无评论